Top N Per Group: Beginner

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.

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.

Department 10 holds the 4 largest salaries on the table, so the sort fills all 3 slots from department 10 and department 20 never appears. The query runs without an error and returns 3 well-formed rows, which is what makes it dangerous: a dashboard built on it shows 3 engineers under a heading reading 'top earners by department', and the mistake surfaces weeks later when the Sales manager asks where her team went. LIMIT is 1 counter over the whole result, and SQL has no clause meaning 'take 3 from each group'.

Reading the result back is the check that catches it in the room: 3 rows, and 1 value of department_id repeated down the column. A per-group result shows every department the table has, so a result where the group column holds a single repeated value is the signature of a global sort. Say it when you see it: 'every row here is department 10, so this ranked the table rather than each department.'

ORDER BY arranges the rows into 1 sequence and LIMIT cuts that sequence at a fixed position. Both clauses see the table as a single list, and neither knows that department_id divides the list into groups. The question asked for 2 lists, 1 per department, each cut at 3. Sorting by department first, ORDER BY department_id, salary DESC, puts the groups next to each other, and LIMIT 3 still cuts once, so the result is the 3 highest earners of whichever department sorts first. The question needs a cutoff applied once per group, and the sorting clauses have no notion of a group.

The next move, once the missing department is noticed, is a filter: WHERE department_id = 20 with LIMIT 3, then the same for department 10, then UNION ALL to glue the 2 results. On 2 departments it produces the right 5 rows. On a real table it needs 1 query per department, the list of departments has to be known when the SQL is written, and a department created next month is missing from the report until someone edits the query. Interviewers hear this patch and ask 'and if there are 400 departments?', which is the question the approach cannot answer.

A window function computes a value for every row from the rows around it, without collapsing them the way GROUP BY does. The table stays 6 rows and gains a column. ROW_NUMBER is the window function that hands each row a position, and the OVER clause after it says 2 things: which rows share a numbering, and in what order they are numbered. Those 2 things are the group and the sort from the question, written as SQL.

The engine gathers the rows of department 10, sorts them by salary descending and numbers them: 101 at 180,000 gets 1, 102 at 160,000 gets 2, 103 at 140,000 gets 3 and 104 at 120,000 gets 4. The partition changes to department 20 and the counter starts over: 201 at 90,000 gets 1 and 202 at 85,000 gets 2. No row has been dropped at this stage, so the output holds all 6 rows plus rn, and the value of rn on each row is its position inside its own department, which is the per-group cutoff the question needs, written down as data a filter can read.

Leave out PARTITION BY and the numbering runs down the whole table: 201 gets 5 and 202 gets 6. A filter at 3 then returns 3 rows, all from department 10, which is the LIMIT answer from the previous section wearing a window function. Interviewers trace this by hand with 1 question, 'what number does 201 get?', and the answer 5 is the moment the bug shows. The defence is to say the clause before typing it: 'PARTITION BY department_id is what gives each department its own ranking.'

ORDER BY salary DESC inside the OVER clause is where 'highest' is encoded. Drop DESC and the numbering runs upward: 104 at 120,000 gets 1 in department 10, and the filter in the next section returns the 3 lowest earners starting from 104, so the 4th earner replaces the top earner 101. Department 20 looks right only because both of its rows survive either way, which is why the bug slips past a quick read. 'Most recent' means a timestamp descending, 'cheapest' means a price ascending, 'best-selling' means a unit count descending. Say the direction when you say the column: 'ordered by salary descending' is the whole phrase, and an interviewer who hears only 'ordered by salary' waits to see which way it goes.

'The 2 most recent orders per customer' changes 3 tokens and nothing else: the cutoff moves to 2 after PARTITION BY customer_id and ORDER BY order_date DESC go in. 'Cheapest listing per seller' is PARTITION BY seller_id ORDER BY price ASC with a cutoff of 1. The clause has 3 slots for the question to fill. The group is one; the sort column and its direction are the other 2. Once those 3 have been said aloud, writing the clause is transcription, and the interviewer can check the transcription against the sentence you gave.

A follow-up around 2 people earning the same salary is common once this query runs. ROW_NUMBER gives tied rows 2 different positions, and the order among them is not guaranteed: it can change from run to run, even on the same engine. The answer that scores is to say so and ask whether the report wants exactly 3 rows or everyone who ties into the top 3. The 3 ranking functions and their tie rules have a lesson of their own.

The most common live error on this question lands 20 seconds after the pattern is recognized. rn is defined in the SELECT list, WHERE rn <= 3 is added to the same query, and the run fails with 'column rn does not exist'. The reason is the order in which the engine evaluates clauses, and an interviewer who sees the error asks for that reason, so explaining it correctly turns the fumble into a point.

WHERE runs in step 2 and rn appears in step 4, so at the moment WHERE is evaluated the column has no value and no name. This is also why the numbering has to finish before the cutoff can be applied: ROW_NUMBER needs to see every row of a department to know which one is third, and a filter that ran before it would change the rows being numbered. The cutoff has to sit in a query that starts after the numbering query has finished, which means a second SELECT that reads the first.

The CTE named ranked is the whole table with rn attached, 6 rows. The outer query's FROM reads ranked, so by the time its WHERE runs, rn is a base column of the thing being read and the comparison is allowed. The filter keeps rows numbered 3 or lower: department 10 loses 104 at position 4, department 20 keeps both of its rows at positions 1 and 2, and the result has 5 rows. Add 10 people to department 20 and the query returns 3 of them; shrink department 10 to 2 people and it returns those 2. The query never asks how large a group is.

A subquery in the FROM clause does the same job with the same rows: the numbering SELECT goes inside parentheses as FROM (...) AS ranked, and WHERE rn <= 3 follows it. The CTE reads better in the room because the numbering step has a name you can point at, and interviewers follow a named step more readily than a nested one. Snowflake offers QUALIFY rn <= 3, as do BigQuery and DuckDB: a clause that runs after the window functions and saves the second SELECT. It's worth naming as a dialect shortcut, with the CTE as the version that runs everywhere.

rn <= 3 and rn < 4 keep the same rows. rn < 3 keeps 2 per department, the off-by-one that returns the right number of groups and the wrong number of rows. rn = 1 keeps the top row of each department and is the deduplication query. rn > 3 keeps everything past the top 3, the rows a 'who is outside the top 3' question wants. The comparison is the last decision in the query and the one most often typed without being read back.