~/learn/SQL · Window functions
Window functions
Aggregate without collapsing your rows.
why this matters
Window functions are the #1 topic in data interviews: rankings, running totals and comparing each row to the one before.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
A window function computes over a set of rows related to the current row, but keeps every row in the output. The OVER clause defines that window.
PARTITION BY restarts the calculation per group; ORDER BY inside OVER makes the window running, which is how you build cumulative totals.
ROW_NUMBER, RANK and DENSE_RANK number rows inside each partition — wrap them in a CTE to pick the top N per group.
Windows keep every row
GROUP BY: 12 rows → 3 rows (one per group)
OVER(): 12 rows → 12 rows (each row gets
an extra column computed from its group)key takeaways
- OVER() adds a calculation without collapsing rows.
- PARTITION BY restarts it per group.
- ROW_NUMBER, RANK and LAG are the everyday ones.
check your understanding
Two products tie for 1st. What does RANK give the next one?
free practice
Build confidence with a question you can solve without Pro.