GROUP BY squeezes many rows into one row per group. That is perfect for "total sales per region", but useless for "each sale, along with its rank inside its region". Window functions solve this: they calculate across a group of rows while keeping every row.
The OVER clause
Every window function has the same shape:
function_name() OVER (PARTITION BY ... ORDER BY ...)PARTITION BYsplits rows into groups (like GROUP BY, but rows are not collapsed).ORDER BYsets the order inside each group, which matters for ranks and running totals.
We will use a table of monthly sales for a fictional electronics chain, Voltmart, with columns region, store, month and revenue.
Ranking: ROW_NUMBER, RANK, DENSE_RANK
SELECT region, store, revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS drnk
FROM store_totals;The three only differ when there are ties. If two stores tie for second place:
| revenue | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 900 | 1 | 1 | 1 |
| 750 | 2 | 2 | 2 |
| 750 | 3 | 2 | 2 |
| 600 | 4 | 4 | 3 |
ROW_NUMBERalways gives unique numbers (ties are broken arbitrarily unless you add more ORDER BY columns).RANKgives ties the same number, then skips.DENSE_RANKgives ties the same number and does not skip.
Top N per group
The most common interview question with window functions is "top 3 per category". You cannot filter on a window function in WHERE, because WHERE runs first. Wrap it in a CTE:
WITH ranked AS (
SELECT region, store, revenue,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk
FROM store_totals
)
SELECT * FROM ranked WHERE rnk <= 3;Choose DENSE_RANK if tied stores should all appear, ROW_NUMBER if you need exactly three rows.
Comparing rows: LAG and LEAD
LAG looks at the previous row in the window; LEAD looks at the next one. This is how you calculate month-on-month change:
SELECT store, month, revenue,
LAG(revenue) OVER (PARTITION BY store ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY month) AS change
FROM monthly_sales;The first month of each store has no previous row, so prev_revenue is NULL. That is correct; do not replace it with zero, or the "change" for the first month will look like a huge jump.
Running totals and moving averages
Add SUM or AVG with an ORDER BY and you get a cumulative calculation:
SELECT store, month, revenue,
SUM(revenue) OVER (PARTITION BY store ORDER BY month) AS running_total,
AVG(revenue) OVER (
PARTITION BY store ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m
FROM monthly_sales;The ROWS BETWEEN part is the frame: which rows around the current one to include. "2 preceding and current row" gives a three-month moving average.
Share of total without a join
A window with no ORDER BY covers the whole partition, so you can compute a share in one pass:
SELECT region, store, revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1) AS pct_of_region
FROM store_totals;Where window functions run
SQL evaluates clauses in roughly this order: FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT, ORDER BY, LIMIT. Two consequences:
- You cannot use a window function in WHERE or HAVING. Use a CTE or subquery.
- You can use a window function over the result of a GROUP BY, for example ranking regions by their total.
Practise the patterns
Window functions appear in almost every analyst SQL test because they separate people who have only done basics from people who can actually analyse. Work through our ranking and LAG, LEAD and running totals sets, then try the SQL interview questions article for the full list of patterns.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: