We use analytics and advertising cookies to understand how the site is used and whether our ads on Facebook and Instagram work. They are set only if you accept. See our Privacy Policy for details.
This problem is paper-only - see explanation for the canonical SQL solution. (LeetCode 184 classic.)
Given Employee(id, name, salary, department_id) and Department(id, name), return one row per department containing the highest-paid employee. If multiple employees tie for the top in a department, include all of them.
For the auto-grader: return [{ department, employee, salary }] sorted by department ascending, employee ascending.
A medium sql problem, graded against 6 test cases (4 of them hidden).
Query-writing problems - joins, aggregation, window functions, and query tuning.
Reach for it when you see: A schema and a question about the data rather than a function signature.
More SQL problems →```sql
WITH ranked AS (
SELECT e.name AS employee, e.salary, e.department_id,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk
FROM Employee e
)
SELECT d.name AS department, r.employee, r.salary
FROM ranked r
JOIN Department d ON d.id = r.department_id
WHERE r.rnk = 1
ORDER BY department, employee;
```
Why `RANK` not `ROW_NUMBER`: the spec says "include all employees who tie for the top". `ROW_NUMBER` would arbitrarily pick one. `RANK` and `DENSE_RANK` both work here (they only differ when there's a 3rd-place after a tie, which doesn't matter for rank = 1).
`(col1, col2) IN (...)` tuple form is cleaner for simple cases but doesn't extend to "top N per group" - the window-function form does.
The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.
Read off this problem's own test suite, so these are the cases a submission actually has to survive.
These apply to the pattern as a whole, not just this problem.