Top 3 Salaries Per Department

Problem

Schemas: Employee(id INT, name VARCHAR, salary INT, dept_id INT), Department(id INT, name VARCHAR). For each department, return the top three distinct salaries: department name, employee name, salary. Ties at any of the three top tiers should all appear.

Constraints

• DENSE_RANK gives "top three distinct", not "top three rows".

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH ranked AS (
  SELECT e.name AS employee, d.name AS department, e.salary,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rk
  FROM Employee e
  JOIN Department d ON d.id = e.dept_id
)
SELECT department, employee, salary
FROM ranked
WHERE rk <= 3
ORDER BY department, salary DESC;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.