Top N per Department

Hook: Interviewers love this one because it checks whether you can rank rows inside each group, not just the whole table.

Question: How do I return the top N rows for each department in SQL?

Answer: Use a window function to assign a rank within each department, then filter in an outer query. The most common choice is ROW_NUMBER() with PARTITION BY department and ORDER BY salary DESC. If ties matter, use DENSE_RANK() instead so equal salaries all make it through the cutoff.

Interview-Ready Answer: I’d solve top N per department with a window function. I partition the rows by department, order each partition by the value I care about, like salary descending, and then assign ROW_NUMBER() if I want exactly N rows per department. I filter in an outer query where that row number is less than or equal to N. If the business rule wants ties included, I switch to DENSE_RANK() so everyone tied at the cutoff is returned.

What it is

Detailed Explanation: A window function is a function that looks at a set of rows related to the current row, but it does not collapse them into one result like GROUP BY does. For top N per department, each department becomes its own little contest, and the database hands out ranks inside that contest.

How it works under the hood

1. The database reads the rows you asked for from FROM and removes rows that fail WHERE.
2. PARTITION BY department splits the remaining rows into separate groups, one partition per department.
3. ORDER BY salary DESC sorts rows inside each partition from highest salary to lowest.
4. ROW_NUMBER() assigns 1, 2, 3, and so on inside each department. This is just a position counter.
5. An outer query filters on that generated number, for example rn <= 3, because window functions are computed after WHERE in SQL’s logical order.
6. The final result keeps the original row details, not just the rank.

Memory model: imagine every department has its own race lane. The window function gives medals within each lane, not across the whole stadium.

When and why to use it

• Use it when you need the best few rows per category: top 3 salespeople per region, latest 2 orders per customer, highest paid 5 engineers per team.
• Use ROW_NUMBER() when you want exactly N rows per department, even if salaries tie.
• Use DENSE_RANK() when you want to include all ties at the cutoff. That may return more than N rows.
• Use a stable tie-breaker, like employee_id, so results are deterministic when two rows have the same salary.

Ranking functions compared

For interview answers, the key sentence is: ROW_NUMBER gives exactly N rows; DENSE_RANK gives all rows tied at the cutoff.

Performance and edge cases

• Time complexity: usually about O(n log n) because each partition must be ordered. If the engine can use a helpful index, it may do less sorting work.
• Space complexity: depends on the database engine, but the sort can use significant memory and spill to disk if the partition is large. On big reporting tables, that is where slow queries often come from.
• Helpful index: a composite index like (department, salary DESC, employee_id) can reduce sort cost in some engines.
• Few rows in a department: if a department has fewer than N employees, you simply get the available rows.
• NULL department: rows with NULL department usually form their own partition because PARTITION BY groups equal values together, including NULLs in most systems.
• Determinism: without a tie-breaker in ORDER BY, equal-salary rows can appear in any order. That is a classic interview gotcha.

Rule of thumb: If the question says “top N per group,” think PARTITION BY + ranking function + outer filter. If it says “include ties,” reach for DENSE_RANK().

Real-World Example: In a payroll analytics dashboard for an HR system, managers want the top 3 earners in each department. A developer once used LIMIT 3 after ordering by salary. That returned only 3 rows total for the whole company, not 3 per department, so most departments looked empty on the dashboard.

What went wrong in production: the chart showed only a tiny slice of the company, executives assumed the ETL job had dropped rows, and support saw tickets like “Engineering disappeared from the report.” The logs were misleading because the query succeeded and returned data; it was the wrong shape of data, not a crash. The fix was to rank rows inside each department with ROW_NUMBER(), then filter by the generated rank.

Follow-up & Tricky Questions:

• How do you include ties? Use DENSE_RANK() instead of ROW_NUMBER(). If the cutoff is rank 2, everyone with rank 1 or 2 stays, even if that means more than N rows.
• How do you make N dynamic? Use a parameter or bind variable, then compare the outer filter to that value, such as rn <= :n. The ranking logic stays the same.
• Can you get top N by multiple columns? Yes. Add more expressions to the window ORDER BY, like salary DESC, hire_date ASC, employee_id. That gives a deterministic order.
• Can you do this without a CTE? Yes, a subquery works too. A CTE is usually easier to read and interviewers like it because it makes the two-step process obvious.
• How do you get bottom N? Reverse the sort order, for example ORDER BY salary ASC. The ranking idea is identical; only the direction changes.
• Why is this better than a correlated subquery? Window functions are usually clearer and often faster because the database can rank once instead of re-scanning many times. A correlated subquery can still be correct, but it is harder to reason about.

• Why doesn’t LIMIT 3 solve it? LIMIT applies to the final result set, so it caps rows globally. You need a per-department rank, which means partitioning first and filtering later.
• Can I filter on the window alias in WHERE? Usually no, because WHERE is evaluated before window functions in SQL’s logical order. That is why you wrap the ranked rows in a CTE or subquery.
• What if two employees have the same salary and no tie-breaker? The engine may return them in either order, so the output can change between runs. Add a stable extra column like employee_id to make the result predictable.

Common Mistakes:

• Using LIMIT instead of partitioning: This returns top rows overall, not top rows per department. Correction: use PARTITION BY department plus a rank filter.
• Filtering in the same SELECT: Many candidates try WHERE rn <= 3 right after defining rn. Correction: put the window function in a CTE or subquery, then filter outside.
• Ignoring ties: A tie at the boundary can silently change business results. Correction: choose ROW_NUMBER() for exact N or DENSE_RANK() for tie-inclusive results.
• Forgetting a tie-breaker: Equal salaries can produce unstable ordering. Correction: add a unique column such as employee_id to the ORDER BY.

Memory Hook: Think: “Each department gets its own podium.” First rank inside the department, then take the medal winners.

Cheat Sheet:

• PARTITION BY = split into independent groups.
• ORDER BY inside the window = define the top order.
• ROW_NUMBER() = exactly N rows.
• DENSE_RANK() = include ties at the cutoff.
• Filter in an outer query, not directly in WHERE.
• Add a stable tie-breaker for repeatable results.

Practice Tasks:

• Change the example to top 3 per department.
• Rewrite the query to return the bottom 2 per department.
• Modify the ranking to order by salary DESC, employee_name ASC and observe how ties change.