185. Department Top Three Salaries
SQL 50 · Subqueries (day 6). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One employee in a top-3 salary of their department.
2. Approach
DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) in a CTE, keep <= 3, JOIN Department for the name.
3. The trap
DENSE_RANK, not ROW_NUMBER: equal salaries all qualify.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
WITH r AS (
SELECT departmentId, name, salary,
DENSE_RANK() OVER (PARTITION BY departmentId
ORDER BY salary DESC) AS rnk
FROM Employee
)
SELECT d.name AS Department, r.name AS Employee, r.salary AS Salary
FROM r
JOIN Department d ON r.departmentId = d.id
WHERE r.rnk <= 3;