SQLQuestion 06 of 14
Top 3 salaries in each department
Answer out loud or in your head first, then compare.
Answer
First I establish whether it's three people or three distinct salary levels. Below I return everyone at the three highest pay levels.
SQL
-- employees(id, department_id, salary)
WITH ranked AS (
SELECT id, department_id, salary,
DENSE_RANK() OVER (
PARTITION BY department_id ORDER BY salary DESC
) AS place
FROM employees
WHERE salary IS NOT NULL
)
SELECT id, department_id, salary
FROM ranked
WHERE place <= 3
ORDER BY department_id, salary DESC, id;Ties: DENSE_RANK gives 1,2,2,3; RANK gives 1,2,2,4. ROW_NUMBER numbers each person separately — with it I add id to the ordering for a deterministic choice.
How did it go? Be honest — “Not yet” puts the question on your review list.