Ranking at the Right Grain

Concepts covered: Window Partitioning, Row Numbering, Common Table Expressions

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.

Say the grain before the syntax

Open by stating what 1 row of the ranked set represents: 'I need 1 row per employee carrying the salary that should count, then I rank those rows inside each department.' That sentence does 2 jobs in the room. It shows the interviewer you compared the input's grain with the question's, and it commits you to a shaping step before the window function. The ranking itself is 2 lines; getting the right rows to it is the rest of the query.

Here is department 10 as it arrives. Employee 103 has 3 pay-change rows, and her current salary is the 150000 row effective 2023-01-01. Employee 105 has held 120000 since 2017.

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

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.

2 windows at 2 grains

WITH current_pay AS (
  SELECT employee_id, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY employee_id
           ORDER BY effective_date DESC
         ) AS latest
  FROM salary_history
),
ranked AS (
  SELECT employee_id, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rn
  FROM current_pay
  WHERE latest = 1
)
SELECT employee_id, salary, rn
FROM ranked
WHERE rn <= 3;

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.

When the shaping step is an aggregate

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.

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.