Top-N per group
The pattern in every analytics interview — top 3 products per category, latest order per user, best score per team — and the wrap-and-filter idiom behind it.
What you'll learn
- Why WHERE ROW_NUMBER() <= N doesn't work directly
- The wrap-and-filter idiom: window in the inner query, filter in the outer
- Variations — latest-per-group, best-per-group, first-N-per-group
- When Postgres's DISTINCT ON beats it
Before you start
If you have interviewed for an analytics or data role, you have met this question. Top 3 products per category. Latest order per user. Best score per player. Same shape, different nouns — and it is probably the single most useful SQL pattern you will learn this week.
Why you cannot filter directly
The instinctive attempt fails:
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
WHERE rn <= 3; -- ERROR: rn doesn't exist yetA window function is evaluated after the WHERE of its own SELECT, so when WHERE runs, rn has not been computed. The fix is always the same — compute the window in an inner query, then filter in the outer one:
WITH ranked AS (
SELECT category, name, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, name) AS rn
FROM products
)
SELECT category, name, price FROM ranked WHERE rn = 1;On our product catalogue — two Stationery, one Grocery, two Electronics — that returns the dearest item in each category:
| category | name | price |
|---|---|---|
| Electronics | Laptop | 900 |
| Grocery | Coffee | 9 |
| Stationery | Notebook | 6 |
PARTITION BY category restarts the numbering per category, so rn = 1 is each category’s top row. Notice the tiebreaker ORDER BY price DESC, name — without one, “the top row” could shift between runs whenever two rows tie. Always add a tiebreaker.
One pattern, many questions
The skeleton never changes; only the PARTITION BY and ORDER BY do. Swap them and you answer a whole family of questions:
- Latest per group —
PARTITION BY user_id ORDER BY order_date DESC, keeprn = 1. This is the foundation of every “current state” query: each user’s latest subscription, each device’s most recent ping. - First-N per group — order ascending, keep
rn <= N. First five visits, first three purchases. - Bottom-N — flip the
ORDER BYdirection. Worst performers, slowest queries.
And the choice of ranking function matters on ties: ROW_NUMBER keeps exactly one row per group (arbitrary on a tie), while RANK lets genuinely-tied rows share the top spot — a leaderboard where two players really are joint first wants RANK, a dedup that must keep one row wants ROW_NUMBER.
Practice
Quick check
Practice this in an interview
All questionsAssign ROW_NUMBER() (or DENSE_RANK() if ties should be included) partitioned by department and ordered by salary descending, then filter in an outer query or CTE where the rank is 3 or less. You cannot filter on a window function directly in WHERE — it must be wrapped.
Rank salaries with DENSE_RANK() ordered descending and keep rank 2 — it handles duplicate salaries and generalises to the Nth-highest. A correlated subquery (the max salary strictly below the overall max) also works, while LIMIT 1 OFFSET 1 is only safe when no two people share a salary.
Use DENSE_RANK() to assign rank without gaps — the Nth distinct salary value is the row where DENSE_RANK equals N. RANK() would produce wrong results when ties occur, and a plain ORDER BY LIMIT/OFFSET approach ignores ties entirely.
All three are extensions to GROUP BY that produce multiple levels of aggregation in a single query. ROLLUP produces hierarchical subtotals, CUBE produces all possible subtotal combinations, and GROUPING SETS lets you specify exactly which grouping combinations you want.