Top N Per Group: Intermediate

The question names an entity and a grain. 'The 3 highest-paid employees per department' ranks employees at 1 row each. The table on the interviewer's screen rarely sits at that grain. It is salary_history with a row per employee per pay change, or employees joined to project_assignments with a row per project, and a window function numbers whatever rows reach it. Rank history rows and an employee with 2 raises in 3 years can fill all 3 positions in her department by herself: a correct ranking of pay changes and a wrong answer about people.

ROW_NUMBER numbers the 5 rows it receives. Employee 101 takes 1, 103's current 150000 takes 2 and her 2020 salary of 135000 takes 3, so rn <= 3 returns 101 followed by 103 twice. Employee 105, the department's real third at 120000, never appears. Nothing in the output looks off, since salaries descend across 3 rows, and nobody questions it until a department head counts 2 names where 3 were promised and asks where the third person went.

The first window works at the employee grain: PARTITION BY employee_id with ORDER BY effective_date DESC, and latest = 1 keeps 1 row per person. The second works at the department grain over those survivors. Read aloud, the plan is 'collapse to the entity, then rank the entity', and the interviewer hears both grains named. The result has 101 at 180000 and 103 at 150000. Third is 105 at 120000, which answers the question that was asked.

A different interviewer hands over the same question with employees already at 1 row per person, and then the shaping step is nothing. Say so anyway: 'this table is 1 row per employee, so I can rank it directly.' It costs 5 seconds, and it removes the most common way this question goes wrong when the table happens to be the other kind.

A sum is the other common shaping step. 'Top 5 products per category by revenue' handed order_items at the line-item grain needs SUM(amount) grouped by product before any window runs; ranking lines returns the 5 largest single lines, which may all belong to 1 product. A join that fans out does the same damage from the other side. 'Top 3 customers per segment by lifetime value, with their city' joins customers to addresses, which repeats a customer once per saved address, so 1 customer with 3 addresses can fill all 3 slots. The test is the same each time: if the entity the question ranks can appear on more than 1 row of the input, shape first.

A window function runs over the rows that survive FROM and WHERE, before the outer query sees anything, so a predicate inside the CTE shapes what gets numbered and a predicate in the outer query trims what was already numbered. WHERE status = 'active' beside the window removes the terminated row before ROW_NUMBER counts it. The same text written as AND status = 'active' next to rn <= 3 acts on a finished numbering in which she holds rank 1. The predicate is character for character the same in both places, and only 1 of the 2 result sets matches what the interviewer asked for.

Move the predicate to the outer query and 106 is numbered 1, 101 becomes 2, 103 becomes 3, 105 becomes 4. WHERE rn <= 3 AND status = 'active' keeps 101 and 103 and drops 106, so department 10 returns 2 rows. The query runs without an error, so the report owner reads it as a department with 2 people, and the real third-highest active earner, 105, is on nobody's screen.

A NULL salary is a row, and a row competes unless something removes it. PostgreSQL treats NULL as larger than every value, so under ORDER BY salary DESC a record with no salary takes rank 1 in its department; MySQL and SQL Server treat NULL as smallest, so the same row lands last. Before anything else, ask whether a row with no salary belongs in the ranking at all. If the answer is no, which it usually is, WHERE salary IS NOT NULL inside the CTE takes it out of competition. If it must remain, NULLS LAST on the ORDER BY stops it from winning, and section 3 shows that clause in the full query.

'Top 3 carriers by cost last quarter' is the same decision wearing a date. A predicate on delivered_at in WHERE restricts the shipments the sum and the ranking are built from, so the ranking reflects last quarter. Applied after the ranking, it filters finished rows by whatever date the final row happens to carry, and when the ranked row is a carrier total there is no shipment date left to filter on. When the ranked rows are totals built by GROUP BY, a condition on the total, HAVING SUM(cost) > 0, also runs before the window and also changes who competes.

'The 3 most recent orders per customer' ties whenever a batch import stamps 200 orders with the same created_at, and a top 3 by order count per store ties constantly because counts are small integers. The habit worth building is to ask 'can 2 rows share this value?' for every ORDER BY column. The answer is yes for any count or rating out of 5, and for dates without a time and rounded amounts. Saying 'ratings tie heavily, so this is a RANK question unless the carousel has fixed slots' is the kind of sentence that gets written in the interviewer's notes.

ORDER BY salary DESC alone leaves 3 rows at 150000 with no instruction about their order, and the SQL standard says nothing about how peers are sequenced. The engine takes whatever physical order its plan produced: a hash join's bucket order, a parallel scan's merge order, the heap's layout after yesterday's VACUUM. Tuesday's refresh shows 102 as third-highest, Wednesday's shows 104, the data has not changed, and the dashboard owner files a bug that nobody can reproduce because the dev database runs with 1 fewer parallel worker. ROW_NUMBER without a total order is that bug waiting for a plan change.

The animation plays the window over department 10's 5 rows with the full ORDER BY. 101 at 180000 takes 1 alone. The 3 rows at 150000 are sorted by hire_date ASC: 103, hired 2018-09-03, takes 2. 102 and 104 share 2021-06-14 as well, and employee_id ASC puts 102 at 3 and 104 at 4. 105 at 120000 takes 5. Run it on any engine with the rows in any physical order and the same numbers land on the same employees, which is what a top 3 that feeds a bonus list requires.

The middle column is a policy decision, so say that out loud. hire_date ASC encodes 'tenure breaks ties'. performance_rating DESC would encode 'rating breaks ties', and employee_id ASC on its own encodes 'the older record wins', which no HR team would choose if asked. Ask: 'when 2 people are tied at the cutoff, who does the business want in the 3?' The answer becomes the second ORDER BY column, and the query documents the rule. The last column is different in kind: it exists so the ORDER BY is a total order, and it should be the primary key or something equally unique.

The tiebreak belongs in the window's ORDER BY. A candidate who adds ORDER BY salary DESC, employee_id to the outer query has ordered the 3 rows the window already chose and has done nothing about which 3 it chose. The numbering happened inside OVER, and only the ORDER BY inside OVER can change it. The same applies to the dedup window from section 0: PARTITION BY employee_id ORDER BY effective_date DESC keeps an arbitrary row when 2 pay changes share a date, and effective_date DESC, change_id DESC fixes it. Any 'keep 1 row per group' is a top 1 per group with the same requirement.

NULLS LAST is the other clause in that ORDER BY, and section 1 covered why it is there: engines disagree on where NULL sorts. The clause itself does not travel. PostgreSQL and Oracle accept it, and both need it, because their DESC puts NULL first. MySQL and SQL Server reject it as a syntax error, although their DESC already puts NULL last. The spelling that runs on all 4 is ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary DESC. Naming that difference tells the interviewer you have watched a query break after a migration.

COUNT(*) OVER (PARTITION BY department_id) puts each department's active headcount on every row, and the cutoff rn <= CEIL(0.10 * dept_size) turns the percentage into an integer the reader can check by hand. With CEIL, a department of 3 returns 1 person and a department of 25 returns 3. Swap in FLOOR and the department of 3 returns nobody and 25 returns 2. Both are defensible policies. The query makes the policy visible in 1 expression, and that visibility is why this form wins in the room over the shorter ones below.

NTILE(10) OVER (PARTITION BY department_id ORDER BY salary DESC) = 1 reads like 'top decile' and distributes rows as evenly as integers allow, with the remainder going to the first buckets. A 25-person department splits into buckets of 3, 3, 3, 3, 3, 2, 2, 2, 2, 2, so bucket 1 holds 3 people, 12% of the department. A 3-person department fills buckets 1 to 3 with 1 person each, so bucket 1 is 33% of it. PERCENT_RANK() is (rank - 1) / (rows - 1): the top row is always 0, so <= 0.10 always admits at least 1 person, and for a 1-person department the division is defined as 0 rather than failing. CUME_DIST() is rows at or above this one divided by all rows, so the top earner of a 3-person department scores 0.33 and <= 0.10 admits nobody.

A percentage cutoff still lands on a tie. PERCENT_RANK and CUME_DIST are built on RANK, so tied salaries share a value and all cross the line together or none do, which makes them RANK-flavoured by construction. The count form uses ROW_NUMBER, so it needs the total ordering from section 3 to return the same people each month, or RANK() in place of ROW_NUMBER() if tied people at the boundary should all receive the bonus. Say which you chose: 'the cutoff is a headcount, so I break ties by tenure and employee id' is a sentence that settles it.