hardWindow functionsSQL
Top Three Salaries per Department
List everyone whose salary is among the top three distinct salaries in their department, using DENSE_RANK() with PARTITION BY: a senior SQL interview staple.
SQL
Tables: departments(id, name) and employees(id, name, salary, manager_id, dept_id).
A department's high earners are the employees whose salary is one of the three highest distinct salaries in that department. Write a query that returns every high earner with three columns in this order: department, employee and salary. Any row order is fine.
Examples
Example 1
Input: IT salaries: Ana 900, Bo 850, Cy 850, Di 700, Ed 600 Output: department | employee | salary IT | Ana | 900 IT | Bo | 850 IT | Cy | 850 IT | Di | 700 Why: The distinct salaries are 900, 850, 700, 600. The top three are 900, 850 and 700, so four people qualify; Ed's 600 is fourth.
Constraints
- Ties count once when finding the three salary levels, but everyone on those levels is returned.
- A department with fewer than three distinct salaries returns all its employees.
- Departments without employees don't appear.
Hints
Hint 1
Which ranking function gives tied salaries the same rank and leaves no gaps?
Hint 2
Rank inside each department with PARTITION BY dept_id.
Hint 3
Window functions can't be filtered in WHERE directly: rank in a CTE, then filter on the rank.
Solution
Show the solution (try it yourself first)
Approach: DENSE_RANK per department
DENSE_RANK() numbers distinct salary levels 1, 2, 3 within each department, giving tied salaries the same number with no gaps, which matches the definition exactly. RANK() would skip numbers after a tie (900, 850, 850, 700 would rank 1, 2, 2, 4) and drop the 700 earner; ROW_NUMBER() would cut ties arbitrarily.
WITH ranked AS (
SELECT e.name, e.salary, e.dept_id,
DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS r
FROM employees e
)
SELECT d.name AS department, ranked.name AS employee, ranked.salary
FROM ranked
JOIN departments d ON d.id = ranked.dept_id
WHERE r <= 3;Without window functions, count the distinct higher salaries in the same department: WHERE (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary) < 3. It is correct but O(n²) in the worst case.
Complexity
O(n log n) to sort each partition; an index on (dept_id, salary) helps MySQL avoid a separate sort.
Found a mistake in this problem, its tests or the solution?