~/learn/SQL · Analytics patterns
Top N per group
The interview question that comes up more than any other.
why this matters
"Top 3 products per category", "latest order per customer" — this single pattern shows up in interviews more than any other.
| 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
Ranking inside a partition and then filtering on the rank is the standard recipe. The choice of ranking function decides what happens with ties.
Try it: Keep only the top product in each category.
ROW_NUMBER always gives exactly N rows but picks arbitrarily among ties. RANK keeps every tied row and leaves gaps. DENSE_RANK keeps ties without gaps. Say out loud which behaviour the question wants.
The same pattern works on aggregates: aggregate first in a CTE, then rank the summary rows.
The pattern
ROW_NUMBER() OVER ( PARTITION BY category ← restart per group ORDER BY revenue DESC ← rank within group ) then filter WHERE rn <= 3
Common gotcha
You cannot filter on a window function in WHERE. Wrap it in a CTE or subquery first, then filter.
Pro tip
ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after a tie, DENSE_RANK doesn't. Pick deliberately.
key takeaways
- PARTITION BY restarts numbering per group.
- Filter the ranking in an outer query or CTE.
- ROW_NUMBER vs RANK vs DENSE_RANK differ only on ties.
check your understanding
Two products tie for 1st. Which function gives them both 1 and the next product 2?
free practice
Build confidence with a question you can solve without Pro.