Department Top Three Salaries in SQL

Question

Given `employee` (id, name, salary, departmentId) and `department` (id, name), write a query to find employees who earn a salary in the top 3 unique salaries of their department.

Accepted Answer

Optimal Solution:
WITH ranked_salaries AS (
  SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary,
    DENSE_RANK() OVER (
      PARTITION BY e.departmentId 
      ORDER BY e.salary DESC
    ) AS rnk
  FROM employee e
  JOIN department d ON e.departmentId = d.id
)
SELECT Department, Employee, Salary
FROM ranked_salaries
WHERE rnk <= 3
ORDER BY Department, Salary DESC;

Technical Explanation:
Using `DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC)` ensures ties count as the same ranking tier. Filtering `rnk <= 3` captures all employees in the top 3 salary tiers.

emp_name	salary	department_name
Joe	85000	IT
Henry	80000	IT
Sam	60000	IT
Max	90000	IT
Janet	69000	Sales
Randy	85000	Sales

Department	Employee	Salary
IT	Max	90000
IT	Joe	85000
IT	Henry	80000
Sales	Randy	85000
Sales	Janet	69000