SQL Window Functions: Rolling Averages Interview

Window functions are one of the clearest signals in a SQL interview. A candidate either understands OVER, PARTITION BY, and frame clauses, or they do not, and there is no way to fake it. This guide walks through the exact rolling average question you are likely to be asked, solved in both SQLite and PostgreSQL.

Why Interviewers Ask This

Window functions separate candidates who can write basic SELECT statements from candidates who can do real analytics. They come up constantly in roles touching time series data, such as daily revenue, rolling engagement metrics, and payment volumes, which is why companies with heavy analytics pipelines lean on them so hard in interviews.

Unlike GROUP BY, a window function lets you compute an aggregate without collapsing rows. That is the entire point, and it is also the most common thing candidates get wrong.

The Question

Given a table of daily revenue per store, write a query that returns each row along with a 3-day rolling average of revenue, calculated per store.

Schema

CREATE TABLE daily_revenue (
  id INTEGER PRIMARY KEY,
  store_id INTEGER,
  revenue_date DATE,
  revenue NUMERIC
);

Sample Data

store_id	revenue_date	revenue
101	2024-01-01	500
101	2024-01-02	700
101	2024-01-03	600
101	2024-01-04	900
102	2024-01-01	300
102	2024-01-02	350

Step-by-Step Approach

1. Partition the data by store_id. Revenue from one store should never blend into the rolling average of a different store.
2. Order each partition by revenue_date. Rolling averages only make sense with a defined order.
3. Define the frame explicitly with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. This is the part almost everyone forgets, and it is the difference between a correct 3-day window and a silently wrong average of everything up to that row.

SQLite Solution

SELECT
  store_id,
  revenue_date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY store_id
      ORDER BY revenue_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) AS rolling_3day_avg
FROM daily_revenue
ORDER BY store_id, revenue_date;

PostgreSQL Solution

SELECT
  store_id,
  revenue_date,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY store_id
      ORDER BY revenue_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::NUMERIC, 2
  ) AS rolling_3day_avg
FROM daily_revenue
ORDER BY store_id, revenue_date;

The only real difference is the explicit cast to NUMERIC before ROUND in PostgreSQL. SQLite is more forgiving about implicit type coercion here.

RANK, DENSE_RANK, and ROW_NUMBER at a Glance

Interviewers frequently pair the rolling average question with a ranking follow-up, so know the difference cold.

Function	Ties handled by	Gap after a tie
ROW_NUMBER()	Arbitrary unique number per row	Not applicable, no ties possible
RANK()	Same rank for ties	Yes, skips the next number
DENSE_RANK()	Same rank for ties	No gaps

Common Mistakes

1. Confusing window functions with GROUP BY. GROUP BY collapses rows into one per group. A window function keeps every row and adds a computed column alongside it. If the row count changes, the wrong tool was used.
2. Skipping the frame clause. Without ROWS BETWEEN, the default frame behaves unpredictably around duplicate ORDER BY values. Always be explicit.
3. Filtering on a window function result in WHERE. This throws an error in both engines, since window functions are evaluated after WHERE. Wrap the query in a CTE or subquery and filter in an outer WHERE instead.
4. Forgetting PARTITION BY. Omit it and the query computes one rolling average across every store combined, a subtle bug that often passes a quick glance but fails on real data.

Frequently Asked Questions

What is the difference between a window function and an aggregate function?

An aggregate function, such as a plain AVG with GROUP BY, collapses multiple rows into one. A window function computes a similar calculation but keeps every original row, adding the result as an extra column through OVER.

Can a window function be used directly in a WHERE clause?

No. WHERE is evaluated before window functions are computed. To filter on a window function result, place the window function in a CTE or subquery, then filter in the outer query.

Which companies commonly ask window function questions?

Any company with time series or engagement data leans on these heavily, particularly payments platforms, social and media companies, and e-commerce analytics teams.

Keep Practicing

Reading a solution is one thing. Writing it under interview pressure is another. Practice window function questions interactively with instant feedback in both SQLite and PostgreSQL using our SQL practice questions, or work through a full business scenario in our case studies.