Top N Percent of Each Group

Concepts covered: Bucketing, Cumulative Distribution, Aggregate Window Fns, Counting

'The top 10% of earners in each department' sounds like the same question with a different cutoff, and the cutoff is where it changes. In a 100-person department, 10% is 10 people. In a 25-person department it is 2.5 people, and in a 3-person department it is 0.3 of a person. Someone decides how 2.5 and 0.3 become row counts, and if you do not, the function you reach for decides silently and differently from the function the last analyst used. The interviewer is listening for whether you name the rounding rule before the smallest department forces it on you.

Count the group, then cut

WITH ranked AS (
  SELECT employee_id, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, hire_date ASC, employee_id ASC
         ) AS rn,
         COUNT(*) OVER (PARTITION BY department_id) AS dept_size
  FROM employees
  WHERE status = 'active'
)
SELECT department_id, employee_id, salary
FROM ranked
WHERE rn <= CEIL(0.10 * dept_size);

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.

What NTILE and PERCENT_RANK decide for you

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.

At 100 rows the 4 methods agree, which is why they look interchangeable on a demo dataset. Everything interesting happens below 25, and the 3-person department is the row to point at in the room: 3 methods return its top earner and 1 returns nobody. That is the question to ask before writing: 'a department of 3 people, does its top 10% have 0 people in it or 1?' The answer picks the rounding, and the rounding picks the SQL.

Ties against a percentage cutoff

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.

The question arrives dressed as 'top decile', 'the 90th percentile and above' or 'the top 5% of accounts by spend in each region'. Each of those is a percentage of a group whose size varies, and each needs the same 2 answers before any function is typed: how a fraction of a person becomes a count, and what the smallest group gets. Decile and percentile wording pulls candidates toward NTILE and PERCENT_RANK, and the strong move is to translate the wording into a count rule first and pick the function second.

Do

• State the grain of the ranked rows before writing any window function
• Put competition filters in the CTE's WHERE, output filters in the outer query
• Ask whether ties at position N appear, then choose the function from the answer
• End every ROW_NUMBER ORDER BY with a unique column
• Make percentage cutoffs an explicit CEIL or FLOOR of the group count

Avoid

• Rank a table whose rows repeat the entity the question is about
• Trust the engine's default NULL placement in a DESC sort
• Add the tiebreak to the outer ORDER BY and expect it to change which rows win
• Use NTILE to mean a fixed percentage on groups smaller than the bucket count
• Call the 3 ranking functions separate tools when only their tie rule differs

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.