"Find the Top 3" Means Ranking
Concepts covered: Window Partitioning
'Show me the 3 best-selling products in every category.' 'Pull the 2 most recent orders for each customer.' 'Who are the 3 top earners per department?' Each sentence pairs a number with a ranking word, then adds a grouping word, and the grouping word decides the query. A ranking word on its own asks for 1 sort of the table and N rows off the top. A ranking word next to 'per', 'each', 'every' or 'within' asks for a ranking inside every group and up to N rows from each, and the result holds as many groups as the table does.
A grouping word and a ranking word in 1 sentence
The tell is a grouping word and a ranking word in the same sentence, usually with a number between them. 'Per department' names the group, 'highest' names the direction of the sort, 'salary' names the column being sorted and '3' names the cutoff. Those 4 pieces are the whole specification, and reading them out in that order (group, sort column, direction, N) is the first move the interviewer is listening for. Say the group column's name aloud: 'per department' means department_id, 'for each customer' means customer_id. Naming it before any SQL exists shows the sentence was parsed rather than matched to the nearest query remembered from a tutorial.
| Question | Group column | Sort column and direction | N |
|---|---|---|---|
| 3 top earners per department | department_id | salary DESC | 3 |
| 2 most recent orders for each customer | customer_id | order_date DESC | 2 |
| Best-selling item in every category | category_id | units_sold DESC | 1 |
| Cheapest listing per seller | seller_id | price ASC | 1 |
| Within each class, the 3 highest scores | class_id | score DESC | 3 |
Read down the table and the pattern shows itself: every row fills the same 4 slots, and the only variation is which column fills each one. The question's nouns give the group and the sort column, its adjective gives the direction, and its number gives N. A question that fills all 4 slots is this pattern whatever the domain, and a question that fills only the last 3 is a plain sort with a cutoff.
The disguises it wears
- 'The latest record for each customer': timestamp sort column, descending, N is 1
- 'For every store, the top performer': 'top' carries both the direction and N, and N is 1
- 'Within each region, the 5 largest accounts': 'within' is the grouping word, account size the sort column
- 'One row per order_id, the most recently loaded one': duplicates after a reload, the same query with N at 1
- 'The 3 cheapest flights on each route': the sort runs ascending, the direction most answers forget to say
N at 1 is the version that shows up most at work. A table loaded twice holds 2 copies of every order, and 'keep the most recently loaded copy of each order' reads as top 1 per order_id ordered by load time descending. Interviewers accept the per-group ranking as the standard deduplication answer, so recognizing N at 1 as the same question covers 2 common screens with 1 pattern. The ranking column changes from a salary to a timestamp and the number changes from 3 to 1, and the query underneath keeps its shape.
What a global top N looks like next to it
- One ranking, N rows in total
- 'Top 10 customers by lifetime spend'
- 'The 5 longest-running queries yesterday'
- 'The highest salary in the company'
- Answered by ORDER BY and LIMIT, and the result holds at most N rows
- One ranking per group, N rows from each
- 'Top 10 customers by spend in each country'
- 'The 5 longest-running queries per user'
- 'The highest salary in each department'
- Answered by a numbering that restarts in every group, and the result holds up to N rows for each distinct group value
The right-hand column is a different query from the left, and a reader of the result can tell them apart by counting groups. 'Top 10 customers in each country' with 40 countries in the table returns up to 400 rows: 10 for each country that has at least 10 customers, and every customer for a country with fewer. The left-hand column returns 10 rows whatever the table holds. When the number of rows in the answer depends on how many groups the table has, the question is a per-group ranking.
About this lesson
This section is part of the Top N Per Group: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.