BeginnerSQL · 25 min

Top N Per Group: Beginner

Top N per group is the single most-asked SQL question in data engineering interviews. Top 3 highest-paid employees in each department. Top 5 best-selling products per category. Top 10 most-recent orders per customer. Every company asks some version of this, often as the first technical question in the loop. The reason candidates lose points here is not the SQL itself. It is failing to spot the pattern in the first ten seconds. Once you can name it on sight, the rest of this lesson is muscle memory.

Spot a top-N-per-group question the moment the interviewer says the words "per" or "each"

Write the canonical ROW_NUMBER + filter solution from muscle memory

Pick the right ranking function for the tie-handling rule the interviewer cares about

Have a fallback ready when the interviewer says "no window functions"

"Find the Top 3" Is Never About Sorting

Daily Life

Interviews

Identify when a question requires per-group ranking instead of a global ORDER BY LIMIT.

When an interviewer says 'top 3 highest-paid employees,' a brand-new candidate hears 'ORDER BY salary DESC LIMIT 3.' That answers a different question. ORDER BY LIMIT gives you the top 3 in the entire company. The interviewer asked for the top 3 in each department. Those are not the same. If your company has one wildly overpaid team, every result will come from that team and every other department will be invisible.

You are being tested on top N per group when you hear:

▸"top N highest/lowest X per group"
▸"the most-recent N records for each customer"
▸"for every department, return the top performer"
▸"within each category, list the best-selling items"
▸Any question with the word per or each followed by a group

What the interviewer is actually testing

This question lives in nearly every SQL screen because it separates candidates in seconds. Reading the prompt carefully and saying 'this is a top-N-per-group question, I'll partition by department and use ROW_NUMBER' signals three things at once: you read carefully, you know the pattern, and you reach for window functions. Writing ORDER BY salary DESC LIMIT 3 signals the opposite. The interviewer is not catching you on syntax. They are checking whether you parse English into the right SQL shape.

What you signal by saying "top N per group" out loud:

▸You read the prompt carefully
▸You know the pattern by name
▸You reach for window functions, not nested loops

Say the words 'partition by' out loud the moment you spot the pattern. That single phrase tells the interviewer you saw it. Most candidates start typing before they say anything. The scorecard rewards naming the approach more than writing the right code.

The 10-second decision

Read the question. If you see the word 'per' or 'each' followed by a group name, and the word 'top' or 'highest' or 'best' or 'most' followed by a number, you are looking at top N per group. Do not write SQL yet. Say one sentence: 'Since we need the top N within each group, I'll partition by the group column and use ROW_NUMBER ordered by the ranking column. Then I'll filter to keep only rows where the rank is at most N.' That sentence is your opening. Say it before you touch the keyboard.

•Weak opening

"I'll use ORDER BY salary DESC LIMIT 3."
Starts typing immediately
Returns 3 rows total, not 3 per department
Has to be told the answer is wrong

✓Strong opening

"This is top N per group. I'll partition by department."
Names the pattern before writing SQL
Reaches for ROW_NUMBER OVER (PARTITION BY ... ORDER BY ...)
Filters where rank <= N in an outer query

Why companies care

This pattern appears in every reporting dashboard. Top contributors per team. Most-recent transactions per account. Best-performing campaigns per region. Highest-spending customers per market. If you cannot write this query, you cannot build the dashboards that matter. That is why the question opens so many interview loops. It is not a trick. It is a basic competence check.

Why ORDER BY ... LIMIT Gets It Wrong

Daily Life

Interviews

Write a correct top-N-per-group query using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) with an outer filter.

The canonical solution to every top-N-per-group question has the same shape. Compute a row number inside each group, ordered by the ranking column. Wrap it in a CTE or subquery. Filter the outer query to keep only rows where the row number is at most N. That is it. Memorize this shape. Write it without thinking.

The query you should be able to write from memory

	/* Top 3 highest-paid employees in each department */
	WITH ranked AS (
	SELECT
	department_id,
	employee_id,
	salary,
	ROW_NUMBER() OVER (
	PARTITION BY department_id
	ORDER BY salary DESC
	) AS rn
	FROM employees
	)

	SELECT
	department_id,
	employee_id,
	salary
	FROM ranked
	WHERE rn <= 3

Read this query out loud while you type it. PARTITION BY department_id is what makes each department its own ranking universe. ORDER BY salary DESC is what makes "highest" mean highest. ROW_NUMBER assigns 1, 2, 3, ... within each partition. The outer WHERE keeps rows where rn is 1, 2, or 3. The result has the top 3 from each department, no matter how many departments exist or how their salaries compare to other departments.

Three things this query does at once:

▸PARTITION BY isolates each department into its own ranking universe
▸ORDER BY defines what 'highest' means inside each partition
▸Outer WHERE filters the rank to keep only the top N

Why the outer filter is required

You cannot put WHERE rn <= 3 in the same SELECT as the ROW_NUMBER. SQL evaluates WHERE before window functions. The rn column does not exist yet when WHERE runs. You have to compute rn first (in a CTE or subquery), then filter on it in an outer query. The classic failure here is collapsing both steps into one SELECT and watching the engine throw a syntax error live. The CTE is not optional. Write it, name it, then filter.

If your database supports it, you can use a subquery instead of a CTE: SELECT ... FROM (SELECT ..., ROW_NUMBER() OVER ... AS rn FROM employees) WHERE rn <= 3. Same result, slightly less readable. In an interview, write the CTE. CTEs read top-to-bottom; subqueries read inside-out. Readability is a scorecard item.

Walk through the trace, and the bug it catches

Suppose the employees table has two departments. Engineering has Alice ($180k), Bob ($160k), Carol ($140k), Dave ($120k). Sales has Eve ($90k), Frank ($85k). After the ROW_NUMBER step, Alice gets rn=1, Bob rn=2, Carol rn=3, Dave rn=4 in Engineering. Eve rn=1, Frank rn=2 in Sales. The outer filter keeps rn <= 3. Alice, Bob, Carol pass. Dave is dropped. Eve, Frank both pass (rn=2 is <= 3). Result: 5 rows. If Sales had ten employees, only the top 3 would survive. If Engineering had two, only those two would appear. The query works no matter the per-group counts. The most common failure on this trace is forgetting the PARTITION BY. Candidates sometimes write ROW_NUMBER() OVER (ORDER BY salary DESC) without the partition, and the ranking runs across the entire table. Alice is 1, Bob is 2, Eve is 3, Carol is 4, and the filter rn <= 3 returns 3 rows total, not 3 per department. This is the same bug as ORDER BY salary DESC LIMIT 3, dressed up to look like a window function. The interviewer will trace through your query and watch for this. Always state out loud: 'PARTITION BY department_id is what makes each department its own group.'

TIP

Before you write SELECT, say what you are partitioning by and what you are ordering by. 'I'm partitioning by department and ordering by salary descending.' The interviewer is scoring whether you can articulate the partition before you produce the code.

PARTITION BY Plus ROW_NUMBER

Daily Life

Interviews

Choose between ROW_NUMBER, RANK, and DENSE_RANK based on whether ties should inflate, collapse, or be broken arbitrarily.

The interviewer will ask 'what if two employees have the same salary?' This is the tie question, and it is the most common follow-up on top N per group. The right answer depends on what 'top 3' means in the business context. ROW_NUMBER gives one answer. RANK gives another. DENSE_RANK gives a third. Picking the wrong one produces wrong row counts and a wrong report.

ROW_NUMBER: always exactly N

ROW_NUMBER assigns 1, 2, 3, ... arbitrarily for ties. If two employees both earn $150k and tie for second place, ROW_NUMBER picks one to be rn=2 and the other to be rn=3. The choice is non-deterministic unless you add a tiebreaker column to ORDER BY. Filtering rn <= 3 gives you exactly 3 rows per group, no matter how many ties exist. Use ROW_NUMBER when the business needs exactly N rows.

Why a tiebreaker column matters:

▸Without one: same query returns different rows on consecutive runs
▸Without one: impossible to reproduce in dev environments
▸Without one: bug surfaces only after a data refresh in prod
▸With one: same row wins every time, every refresh

RANK: ties get the same rank, then a gap

RANK assigns 1, 2, 2, 4, ... If two employees tie for second, both get rank=2 and the next employee gets rank=4. Rank=3 is skipped. Filtering rank <= 3 gives you 3 rows in this case (positions 1, 2, 2). But if the tie were larger, you would get more rows than expected. Use RANK when the business says 'top 3 including ties.'

DENSE_RANK: ties get the same rank, no gap

DENSE_RANK assigns 1, 2, 2, 3, ... Two employees tied for second both get rank=2, and the next gets rank=3. No gap. Filtering rank <= 3 returns everyone whose salary is among the top 3 distinct values. If five employees earn the top 3 distinct salaries (say $200k, $200k, $180k, $160k, $160k), you get all five rows. Use DENSE_RANK when the business says 'top 3 salary levels' rather than 'top 3 employees.'

Function	1st value	Tie at 2nd	Tie at 2nd	Next	Returns top 3 means
ROW_NUMBER	1	2	3	4	Exactly 3 rows; ties broken arbitrarily
RANK	1	2	2	4	3 distinct positions with possible gaps
DENSE_RANK	1	2	2	3	All rows whose value is in the top 3 distinct levels

When the interviewer asks 'what about ties?', do not just say 'I'll add a tiebreaker.' Name the three functions and which business question each answers. 'If they need exactly 3 rows, ROW_NUMBER. If they need top 3 including ties, RANK. If they need top 3 distinct salary levels, DENSE_RANK.' Showing you know the difference is a strong signal.

NULLs in the ORDER BY

NULLs cause silent ranking bugs. By default, most databases sort NULLs first when sorting ASC and last when sorting DESC (Postgres). Some do the opposite (MySQL). If you rank employees by salary DESC and some employees have NULL salary (intern, on leave, data quality issue), those NULLs land at the top of the ranking and become your 'highest paid.' Always add NULLS LAST or NULLS FIRST explicitly when you order by a column that might be NULL.

ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC NULLS LAST) AS rn

TIP

Even if the column is supposed to be NOT NULL, write NULLS LAST anyway. It is one extra word and it eliminates an entire class of silent bug. The interviewer will notice. It is a marker that you have been burned by NULLs in production.

The interviewer follow-up template

Almost every interviewer follows the initial top-N solution with one of these three probes. Have the answer ready before they ask.

•Probe

"What if two employees tie for third?"
"What if salary is NULL?"
"What if you need a deterministic tiebreaker?"

✓Your answer

"Depends on the business rule. ROW_NUMBER for exactly N, RANK or DENSE_RANK if ties should be included."
"NULLS LAST in the ORDER BY so missing salaries do not become the top of the ranking."
"Add a secondary ORDER BY column like hire_date ASC or employee_id ASC to break ties predictably."

Filtering rn <= N

Daily Life

Interviews

Solve top-N-per-group using a correlated subquery or self-join when the interviewer bans window functions.

Most interviewers let you use window functions. Some do not. If you hear 'now solve it without window functions,' the interviewer is testing whether you understand the underlying mechanics or whether you just memorized ROW_NUMBER. Two fallback approaches work. Know both. The correlated subquery is more common in interviews. The self-join is more common in older codebases.

The correlated subquery approach

Idea: for each row, count how many other rows in the same group have a higher value. If that count is less than N, this row is in the top N. The counting happens in a correlated subquery that runs once per outer row.

	/* Top 3 per department, no window functions */
	SELECT
	department_id,
	employee_id,
	salary
	FROM employees AS e1
	WHERE (
	SELECT
	COUNT(*)
	FROM employees AS e2
	WHERE e2.department_id = e1.department_id
	AND e2.salary > e1.salary
	) < 3

Read this carefully. For each row e1, the subquery counts how many e2 rows share the same department and have a strictly greater salary. If exactly zero rows beat e1, e1 is rank 1. If one row beats e1, e1 is rank 2. If two rows beat e1, e1 is rank 3. The filter < 3 keeps ranks 1, 2, and 3. Ties: if two employees tie for second, neither row has a row strictly greater than the other in the count, so they both pass. This behaves like RANK, not ROW_NUMBER.

The correlated subquery runs once per outer row. On modern engines the optimizer rewrites it into a hash semi-join. On older engines it can be O(N^2). If the interviewer asks about scale, say 'this works but it is O(N^2) on engines that cannot rewrite the subquery.' That sentence is the depth signal.

The self-join approach

Same logic, different syntax. Join the table to itself on the same group key, then count how many self-join partners have higher salaries. GROUP BY and HAVING produce the same filter.

	/* Top 3 per department via self-join */
	SELECT
	e1.department_id,
	e1.employee_id,
	e1.salary
	FROM employees AS e1
	LEFT JOIN employees AS e2
	ON e2.department_id = e1.department_id
	AND e2.salary > e1.salary
	GROUP BY e1.department_id, e1.employee_id, e1.salary
	HAVING COUNT(e2.employee_id) < 3

This is the same idea as the correlated subquery, just unrolled. For each row e1, the LEFT JOIN finds all e2 rows in the same department with a higher salary. COUNT counts them (LEFT JOIN ensures rows with no matches count zero). HAVING keeps rows where fewer than 3 employees beat them. Same RANK-style semantics.

•When you'd use each

Correlated subquery: simpler, easier to read, default reach
Self-join: slightly faster on old engines, easier to extend with extra conditions
ROW_NUMBER: when window functions are allowed

✓What the interviewer wants to hear

"I'd prefer ROW_NUMBER, but I'll write the correlated subquery if window functions are off the table."
"Both fallbacks behave like RANK, so I'll add a tiebreaker if exactly N is required."
"On a modern engine the optimizer rewrites the correlated subquery, but worst case it's O(N^2)."

Talking Through Top-N in an Interview

Daily Life

Interviews

Discuss index strategies, partition pruning, and how to handle "top N with ties" at warehouse scale.

Past the basic solution, the interviewer probes business semantics and operational reality. 'What if there's a tie at position N?' is the semantic probe. 'How does this perform on a billion rows?' is the operational probe. Both have crisp answers if you know them.

Exactly N vs at-least N: the business semantics probe

Suppose the question is 'top 3 highest-paid employees per department.' Now imagine Engineering has Alice ($200k), Bob ($180k), Carol ($180k), Dave ($150k). Carol and Bob are tied for second. Do we return 2 rows, 3 rows, or 4 rows from Engineering? The answer depends entirely on the business rule, and the interviewer wants to hear you ask.

•Business rule

"Give me exactly 3 employees, even if I have to break ties."
"Give me everyone in the top 3 positions, including ties."
"Give me everyone earning a top-3 salary level."

✓Right tool

ROW_NUMBER with a deterministic tiebreaker, rn <= 3
RANK, rank <= 3 (returns Alice, Bob, Carol; Dave excluded)
DENSE_RANK, rank <= 3 (returns everyone at the top 3 distinct salaries)

TIP

Ask the interviewer 'should ties at position N be included or excluded?' before you write the query. This question is what separates candidates who write code from candidates who design solutions. Even if the interviewer says 'use your judgment,' you have just demonstrated that you think about business semantics first.

Situation	Phrasing that flatlines	Phrasing that lands
You see 'top 3 per department'	"I'll ORDER BY salary DESC LIMIT 3."	"This is top N per group. I'll PARTITION BY department_id and ORDER BY salary DESC inside ROW_NUMBER, then filter rn <= 3 in an outer query."
The interviewer asks about ties	"I'll add a tiebreaker."	"ROW_NUMBER if the business needs exactly 3 rows; RANK if 'top 3 including ties' is the contract; DENSE_RANK if they want top 3 distinct salary levels. Which does the consumer expect?"
You realize some salaries are NULL	"I'll filter them out."	"I'd add NULLS LAST to the ORDER BY so NULLs don't silently land at the top of a DESC sort. Whether to filter them depends on whether 'no salary recorded' should appear in the report at all."
The interviewer asks 'how does this scale'	"Window functions are fast."	"The window function sorts each partition; a composite index on (department_id, salary DESC) lets the engine walk the index in partition order without a sort step. For a dashboard, I'd materialize the result nightly rather than re-rank on every page load."
The interviewer says 'no window functions'	"I'm stuck."	"Correlated subquery counting how many same-group rows have a strictly greater value, filtered to < N. Behaves like RANK; on older engines it's O(N²) before the optimizer rewrites it."

Performance: indexes and partitioning

Window functions are not magic. ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) requires the database to sort each department's rows by salary. The database can do this efficiently if there is an index on (department_id, salary). Without that index, the engine has to scan the whole table and sort it in memory or spill to disk. For 10 million rows across 1,000 departments, the difference is seconds vs minutes.

	CREATE INDEX idx_emp_dept_salary
	ON employees(department_id, salary DESC) ;

Mention the index unprompted. 'For this to scale, I'd want a composite index on (department_id, salary). The window function can then walk the index in order per partition without a sort step.' Most candidates write the query and stop. The candidate who keeps going, names the supporting index, and connects the query to the physical layout has flipped from 'can write SQL' to 'can ship the SQL into a production schema.' That distinction is what gets remembered at the debrief.

At Amazon in 2020, an internal seller-ranking dashboard ran a top-N-per-category query nightly across a billion-row order_items table. The first version computed ROW_NUMBER over the raw table without a supporting index; the job took 47 minutes and consumed enough warehouse credits that finance flagged it. The team added a composite sort key on (category_id, gmv DESC) and changed nothing else in the SQL; the next run finished in under 4 minutes. The lesson the team wrote into their interview rubric was 'a top-N answer that doesn't name the supporting index is incomplete.' Candidates who say 'I'd add a composite index on (department_id, salary DESC) so the window function walks the index in partition order' tend to clear that bar in one sentence.

Partitioning at warehouse scale

On Snowflake, BigQuery, or Redshift, the table is partitioned by date or by a high-cardinality column. The top-N query runs in parallel across partitions. A query that finds the top 3 highest-paid employees per department, with the table partitioned by hire_year, will scan every partition because department_id is not the partition key. If the workload is frequent, consider a clustered or sorted layout that aligns with the typical query pattern.

If the interviewer asks 'how would you compute this incrementally?', the answer is 'I would not.' Top N per group is not an incremental computation; one new row can change every rank. The pattern is to recompute the top N nightly into a separate dashboard table, and serve from that. Saying this shows you understand the gap between OLTP and OLAP.

The closing you can memorize

After solving the problem, deliver a three-sentence wrap. 'I used ROW_NUMBER partitioned by department, ordered by salary descending, then filtered to rn <= 3. I would add NULLS LAST for missing salaries and a secondary ORDER BY for deterministic tiebreaking. For scale, a composite index on (department_id, salary) and a nightly materialization if this powers a dashboard.' Three sentences. Each hits a different rubric item. Swap the column names for whatever the question asked about.

❯❯❯PUTTING IT ALL TOGETHER

> You are in an Amazon data engineering screen. The interviewer asks: 'Find the top 3 highest-paid employees in each department.'

You say: 'This is a top N per group question. I will partition by department and use ROW_NUMBER ordered by salary descending. Then I will filter where rn is at most 3 in an outer query.'

You write the CTE in twelve lines: ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) inside the CTE, WHERE rn <= 3 outside.

Trace one department on the whiteboard: Alice 200k rn=1, Bob 180k rn=2, Carol 160k rn=3, Dave 150k rn=4. Filter keeps the first three.

Follow-up: 'What if Bob and Carol both earn 180k?' You say: 'ROW_NUMBER picks one arbitrarily. If exactly 3 are needed, I add a secondary ORDER BY like hire_date or employee_id. If both should be included, RANK is the right tool.'

KEY TAKEAWAYS

'Top 3 per department' is not ORDER BY with LIMIT, which returns the top 3 company-wide and lets one overpaid team crowd out every other group. The trigger is 'per' or 'each' next to 'top', 'highest', or 'most'.

The canonical shape is ROW_NUMBER over PARTITION BY department_id ORDER BY salary DESC inside a CTE, then WHERE rn <= 3 in the outer query.

The outer query is required, not stylistic: WHERE is evaluated before window functions, so rn does not exist yet in the same SELECT that computes it.

Ties pick the function: ROW_NUMBER for exactly N rows, RANK for top N including ties with a gap after them, and DENSE_RANK for the top N distinct values, which can return five rows when salaries repeat.

NULLs sort first on DESC in some engines and become fake top earners, so write NULLS LAST and add a deterministic tiebreaker column to the ORDER BY.

Without window functions, count how many rows in the group beat each row via a correlated subquery or a LEFT JOIN with HAVING COUNT(...) < 3; both carry RANK-style tie semantics and go quadratic on engines that cannot rewrite them.

The single most common SQL interview question, hiding in plain sight

Category: SQL
Difficulty: beginner
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: "Find the Top 3" Is Never About Sorting, Why ORDER BY ... LIMIT Gets It Wrong, PARTITION BY Plus ROW_NUMBER, Filtering rn <= N, Talking Through Top-N in an Interview

Lesson Sections

"Find the Top 3" Is Never About Sorting (concepts: sqlRowNumber)
When an interviewer says 'top 3 highest-paid employees,' a brand-new candidate hears 'ORDER BY salary DESC LIMIT 3.' That answers a different question. ORDER BY LIMIT gives you the top 3 in the entire company. The interviewer asked for the top 3 in each department. Those are not the same. If your company has one wildly overpaid team, every result will come from that team and every other department will be invisible. What the interviewer is actually testing This question lives in nearly every SQL
Why ORDER BY ... LIMIT Gets It Wrong (concepts: sqlRowNumber)
The canonical solution to every top-N-per-group question has the same shape. Compute a row number inside each group, ordered by the ranking column. Wrap it in a CTE or subquery. Filter the outer query to keep only rows where the row number is at most N. That is it. Memorize this shape. Write it without thinking. The query you should be able to write from memory Why the outer filter is required You cannot put WHERE rn <= 3 in the same SELECT as the ROW_NUMBER. SQL evaluates WHERE before window fu
PARTITION BY Plus ROW_NUMBER (concepts: sqlRankDenseRank)
The interviewer will ask 'what if two employees have the same salary?' This is the tie question, and it is the most common follow-up on top N per group. The right answer depends on what 'top 3' means in the business context. ROW_NUMBER gives one answer. RANK gives another. DENSE_RANK gives a third. Picking the wrong one produces wrong row counts and a wrong report. ROW_NUMBER: always exactly N RANK: ties get the same rank, then a gap RANK assigns 1, 2, 2, 4, ... If two employees tie for second,
Filtering rn <= N (concepts: sqlSubqueryCorrelated)
Most interviewers let you use window functions. Some do not. If you hear 'now solve it without window functions,' the interviewer is testing whether you understand the underlying mechanics or whether you just memorized ROW_NUMBER. Two fallback approaches work. Know both. The correlated subquery is more common in interviews. The self-join is more common in older codebases. The correlated subquery approach Idea: for each row, count how many other rows in the same group have a higher value. If that
Talking Through Top-N in an Interview (concepts: sqlRankDenseRank)
Past the basic solution, the interviewer probes business semantics and operational reality. 'What if there's a tie at position N?' is the semantic probe. 'How does this perform on a billion rows?' is the operational probe. Both have crisp answers if you know them. Exactly N vs at-least N: the business semantics probe Suppose the question is 'top 3 highest-paid employees per department.' Now imagine Engineering has Alice ($200k), Bob ($180k), Carol ($180k), Dave ($150k). Carol and Bob are tied fo