Write a query to return the top 3 highest-paid employees in each department.
Assign ROW_NUMBER() (or DENSE_RANK() if ties should be included) partitioned by department and ordered by salary descending, then filter in an outer query or CTE where the rank is 3 or less. You cannot filter on a window function directly in WHERE — it must be wrapped.
How to think about it
This is one of the most common SQL interview problems, and it tests three things at once: do you know window functions, do you know why you need a wrapper query, and can you reason about ties?
The why-a-wrapper part is the crux. Window functions are evaluated in the SELECT phase, after WHERE has run — so you can’t write WHERE rn <= 3 in the same query that computes rn. You rank in a CTE, then filter in the outer query.
A worked example
PARTITION BY dept restarts the rank per department; ORDER BY salary DESC makes rank 1 the top earner; the outer WHERE rn <= 3 keeps the top three:
WITH ranked AS (
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
)
SELECT dept, name, salary, rn
FROM ranked
WHERE rn <= 3
ORDER BY dept, salary DESC;| dept | name | salary | rn |
|---|---|---|---|
| Eng | Aarav | 120000 | 1 |
| Eng | Bea | 110000 | 2 |
| Eng | Chen | 110000 | 3 |
| Sales | Eli | 80000 | 1 |
| Sales | Farah | 72000 | 2 |
| Sales | Gita | 72000 | 3 |
Three per department, the rank restarting at 1 for Sales. But look closely at the ties: Bea and Chen both earn 110,000, and ROW_NUMBER arbitrarily gave them rn 2 and 3 — a fourth Eng employee on 110,000 would be cut at rn 4 purely by luck of the tiebreak. That’s the decision the interviewer wants you to surface.
Choosing the ranking function
| Requirement | Function |
|---|---|
| Exactly N rows per group, ties broken arbitrarily | ROW_NUMBER |
| Include everyone tied for Nth place | DENSE_RANK |
| Include ties but skip ranks after them | RANK |
Swap to DENSE_RANK and three people sharing the third salary all survive <= 3 — potentially more than three rows per department. Whether that’s correct is a business question, so clarify it before coding. (The pre-window alternative — a correlated COUNT(DISTINCT salary > e1.salary) < 3 — is O(n²) and fragile; mention you know it, then pivot to the window version.)