PARTITION BY Plus ROW_NUMBER

Concepts covered: Row Numbering, Window Partitioning, Sorting Results

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.

Watching the counter restart

SELECT department_id, employee_id, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees

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.

The bug that wears a window function

SELECT department_id, employee_id, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

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.'

The direction is half the answer

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.

Same clause, different question

'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.

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.