SQL Interview Prep · Lesson

Per-Department Top Earner

Combining partitioning with ranking for grouped top-N salary problems.

From Global to Per-Group Ranking

The next escalation: "Find the highest-paid employee in each department." This combines ranking with grouping and is a guaranteed mid-level question.

Assume an employee table with id, name, department_id, and salary. We want one (or more, on ties) top earner per department, not just the global maximum.

The key new tool is PARTITION BY, which restarts the ranking inside each department.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY resets the ranking

Adding PARTITION BY department_id to the window tells the database to compute the ranking independently within each department.

Every department starts its own rank 1. So the top earner in department 1 and the top earner in department 5 both get rank 1. Without partitioning, only the single global maximum would get rank 1.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

All lessons in this course

← Back to SQL Interview Prep