185. Department Top Three Salaries

For each department, return the employees whose salary is among the department's three highest distinct salaries.

"Three highest distinct salaries" is dense_rank() <= 3: tied employees share a rank, and the ranks have no gaps.

Lesson: window functions.

Solutions

SELECT d.name AS Department, t.name AS Employee, t.salary AS Salary
FROM (
    SELECT name, salary, departmentId,
           dense_rank() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rnk
    FROM Employee
) t
JOIN Department d ON d.id = t.departmentId
WHERE t.rnk <= 3;