SQL window functions cheat sheet
A value per row, computed over a group, without collapsing anything. The anatomy, the ranking trio, and the top-N interview pattern.
Get the free PDF
One page, print-ready, free to share. No signup needed.
A window function computes a value per row over a group of related rows, without collapsing anything. That is the entire concept: GROUP BY answers "one number per group", windows answer "every row, with its group context attached".
The anatomy
SUM(amount) OVER (
PARTITION BY city -- restart per group
ORDER BY order_date -- running order
) -- one value PER ROW, no row collapses
The ranking trio on a tie
| amount | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 300 | 1 | 1 | 1 |
| 200 | 2 | 2 | 2 |
| 200 | 3 | 2 | 2 |
| 100 | 4 | 4 | 3 |
RANK skips after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). ROW_NUMBER never ties.
Look around: LAG and LEAD
LAG(amount) OVER (ORDER BY order_date) -- previous row
LEAD(amount) OVER (ORDER BY order_date) -- next row
Deltas between periods, retention checks, sessionization: all LAG/LEAD shapes.
Running total
SUM(amount) OVER (ORDER BY order_date) AS running_total
Top-N per group: the interview pattern
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY city
ORDER BY amount DESC
) AS rn
FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;
Top 3 sales per city, latest event per user, best product per category: all this exact shape. Learn it once.
The trap: a window in WHERE
-- wrong
WHERE ROW_NUMBER() OVER (...) <= 3
-- right: name it in a CTE, filter outside
WHERE rn <= 3
Window functions run after WHERE (same execution-order story as the alias error). BigQuery, Snowflake and DuckDB offer QUALIFY as the shortcut; the CTE works everywhere.
Frequently asked questions
- What is the difference between a window function and GROUP BY?
- GROUP BY collapses rows into one per group. A window function computes over the group but keeps every row, attaching the result to each one. Use windows when you need both the detail and the group context, like each sale next to its city total.
- What is the difference between RANK and DENSE_RANK?
- Both give ties the same rank. RANK then skips numbers (1, 2, 2, 4) while DENSE_RANK does not (1, 2, 2, 3). ROW_NUMBER ignores ties entirely and numbers every row uniquely.
- Can I filter on a window function result in WHERE?
- No: window functions are evaluated after WHERE. Wrap the query in a CTE or subquery and filter the outer query, or use QUALIFY on engines that support it (BigQuery, Snowflake, DuckDB).
- What does PARTITION BY do?
- It restarts the window per group, like GROUP BY but without collapsing rows. PARTITION BY city means every city gets its own ranking, running total, or lag sequence.
Get the free PDF
One page, print-ready, free to share. No signup needed.