Which Rows Compete for the Top N

Concepts covered: Filtering, Execution Order, Row Numbering

These 2 requests sound alike and return different rows. 'The 3 highest-paid active employees per department' sets a condition on who competes. 'The 3 highest-paid per department, and show me the active ones' ranks everyone and then hides rows. In the first, a terminated employee holding the department's top salary never enters the ranking, and 3 active people come back. In the second, she takes position 1, the filter removes her afterwards, and the department returns 2 rows. An interviewer who adds 'only active' or 'from last quarter' to the question is checking which of the 2 you build.

Where the condition sits

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.

WITH ranked AS (
  SELECT employee_id, department_id, salary, status,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE status = 'active'
)
SELECT employee_id, salary, rn
FROM ranked
WHERE rn <= 3;

SELECT employee_id, salary, status,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE status = 'active'

The animation plays this over department 10, where employee 106 holds the top salary at 210000 and a terminated status. WHERE tests each of the 4 rows and only 106 fails. ROW_NUMBER numbers the survivors in salary order: 101 at 180000 takes 1, 103 at 150000 takes 2 and 105 at 120000 takes 3. The cutoff keeps all of them, every one active, as the question asked.

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.

Answer in execution order

If the interviewer asks why the position matters, answer in evaluation order. FROM and WHERE run first, GROUP BY and HAVING next, then the SELECT list where window functions compute, and the outer query last. ROW_NUMBER sits in step 4 and sees the output of steps 1 to 3. A predicate in step 2 shapes its input; a predicate in the outer query shapes its output. 'Because the CTE runs first' describes the same fact, and the evaluation-order version shows you can reason about any clause the interviewer adds next.

Nulls compete unless you remove them

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.

Time filters are competition filters

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

About this lesson

This section is part of the Top N Per Group: Intermediate 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.