0Pricing
SQL Interview Prep · Lesson

Per-Department Top Earner

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

Per-Department Top Earner is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

Filtering to rank 1

To keep only the top earners, wrap the ranked query and filter for rank 1. As always, the window function must be computed in a subquery or CTE before you can filter on it.

Using DENSE_RANK (or RANK) here means that if two employees tie for the highest salary in a department, both are returned. That is usually the correct interpretation of "the top earner."

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

ROW_NUMBER when you want exactly one

Sometimes the interviewer wants exactly one row per department even if there is a tie. Then use ROW_NUMBER and add a deterministic tiebreaker, such as the lowest id.

Without the tiebreaker, ties resolve arbitrarily and your result is non-deterministic. Adding , id ASC makes the choice repeatable.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK vs ROW_NUMBER vs RANK here

Choose based on the exact wording:

  • DENSE_RANK = 1: all employees tied for the highest salary per department.
  • RANK = 1: identical to DENSE_RANK for the top rank (gaps only matter below rank 1).
  • ROW_NUMBER = 1: exactly one employee per department, ties broken by your ORDER BY.

Saying which one you picked and why is the part interviewers grade.

The pre-window correlated approach

Before window functions, the standard solution was a correlated subquery: keep a row only if no one in the same department earns more.

This naturally returns all tied top earners. It is portable but can be slow because the inner MAX is evaluated per outer row unless the optimizer rewrites it.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

The GROUP BY join approach

Another portable pattern: compute the max salary per department with GROUP BY, then join back to get the matching employees.

This is efficient and clear. The join brings back every employee whose salary equals their department's max, so ties are preserved.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

Top N per department

The pattern extends to "top 3 earners per department" with no new ideas. Just change the filter to a range.

With DENSE_RANK, rnk <= 3 returns the top three distinct salary levels (possibly more than three rows on ties). With ROW_NUMBER, rn <= 3 returns exactly three rows per department.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Worked example

Department 1: Ana 120, Bob 120, Cara 90. Department 2: Dan 200, Eve 150.

  • DENSE_RANK = 1: Ana (120), Bob (120) from dept 1; Dan (200) from dept 2. Three rows.
  • ROW_NUMBER = 1 with id tiebreaker: one of Ana/Bob (whichever has lower id) plus Dan. Two rows.

Same data, different row counts depending on the function. Pick to match the question.

Including departments and joining names

Interviewers often add a department table and ask for the department name. Just join it on after ranking.

Keep the ranking on the employee table and join the lookup table at the end, so partitioning still happens at the right granularity.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Pitfalls to avoid

Common per-group ranking mistakes:

  • Forgetting PARTITION BY and ranking globally, returning only the company-wide top earner.
  • Using ROW_NUMBER when the question implies ties should all appear, silently dropping co-top-earners.
  • Trying to put the window function in WHERE directly instead of wrapping it.
  • Joining the department table before ranking and accidentally changing partition granularity.

Quick Check

Pick the right ranking function for the requirement.

Recap

Per-department top earner is the global ranking pattern plus PARTITION BY department_id:

  • DENSE_RANK = 1 returns all tied top earners per department.
  • ROW_NUMBER = 1 with a tiebreaker returns exactly one per department.
  • Portable alternatives: correlated MAX per department, or GROUP BY max joined back to the table.

Extend to top-N by changing = 1 to <= N. State your tie-handling choice out loud.

Frequently asked questions

Is the “Per-Department Top Earner” lesson free?

Yes — the full text of “Per-Department Top Earner” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Per-Department Top Earner”?

Combining partitioning with ranking for grouped top-N salary problems. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Per-Department Top Earner” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Second Highest Salary, Five Ways
  2. Nth Highest With DENSE_RANK
  3. Per-Department Top Earner
  4. Returning NULL When No Nth Value Exists
← Back to SQL Interview Prep