SQL & DatamediumSign in to track progress

Top Earners per Department

Given tables employees(id, name, salary, department_id) and departments(id, name), write a query returning each department's name alongside its highest-paid employees, including ties.

Hints

Hint 1

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) handles ties cleanly.

Hint 2

Window functions cannot appear in WHERE — filter on the rank in an outer query or CTE.

Solution

Show the approach

Approach

Rank salaries within each department, then keep rank 1. DENSE_RANK gives every tied top earner rank 1, and the filter has to live outside the window's own query block, so rank in a CTE and filter on it.

WITH ranked AS (
  SELECT d.name AS department,
         e.name AS employee,
         e.salary,
         DENSE_RANK() OVER (
           PARTITION BY e.department_id
           ORDER BY e.salary DESC
         ) AS salary_rank
  FROM employees e
  JOIN departments d ON d.id = e.department_id
)
SELECT department, employee, salary
FROM ranked
WHERE salary_rank = 1
ORDER BY department, employee;

Worth saying out loud

The window version scans employees once and sorts within each department; an index on (department_id, salary DESC) lets it read each partition in order.
The correlated-subquery version, WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.department_id = e.department_id), is fine for the maximum when the planner decorrelates it or the index serves each lookup — but it doesn't generalize.
Top three per department is salary_rank <= 3 here. Say which "top three" is meant: DENSE_RANK gives the top three salaries (possibly more people), ROW_NUMBER exactly three people with an arbitrary tie-break. The subquery equivalent counts higher distinct salaries per row, which is quadratic.

Asked at

Amazon Netflix Snowflake