Write a query to return the top 3 highest-paid employees in each department.
Assign ROWNUMBER() (or DENSERANK() 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.)