The highest-paid employee in each department

Step 1 Solve it yourself first

Tables you will use

employees departments

What it really tests

The most common window-function question: top N per group, including how to handle ties and how to filter on a window function.

The pattern: Top N per group

"In each", "per", "for every" together with "highest", "top 3", "most recent", "first".

Picture it

flowchart LR
  E["employees"] --> P["PARTITION BY dept_id<br/>ORDER BY salary DESC"]
  P --> D1["Engineering: Asha 1, Hana 2, Ben 3, Chen 3"]
  P --> D2["Sales: Dara 1, Eli 2"]
  P --> D3["Marketing: Fatima 1"]
  D1 --> K["keep rank 1 in each"]
  D2 --> K
  D3 --> K

The approach, step by step

1. Ask: if two people tie for top salary in a department, show both? (Yes here.)
2. Rank employees within each department by salary, highest first: RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC).
3. RANK keeps ties together; ROW_NUMBER would pick one at random.
4. Do the ranking in a CTE, then keep rank 1 outside it, and join departments for the name.

WITH ranked AS (
  SELECT d.dept_name, e.name, e.salary,
         RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
  FROM employees e
  JOIN departments d ON d.dept_id = e.dept_id
)
SELECT dept_name, name, salary
FROM ranked
WHERE rnk = 1
ORDER BY dept_name;

SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id
)
ORDER BY d.dept_name;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  Mode SQL Tutorial — SQL Window Functions

  Mode, free

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — top N per group questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why the rank must be computed in a CTE and filtered outside.

   Show answer

   Because of the order a query runs in. WHERE runs before SELECT, and window functions like RANK are worked out in the SELECT step. So when WHERE runs, the rank doesn't exist yet, and WHERE RANK() OVER (…) = 1 is an error.

   The fix is two steps: a CTE (or subquery) works out the rank for every row, and the outer query, which runs afterwards, filters on it like an ordinary column. (Some databases, such as Snowflake and BigQuery, add QUALIFY to filter on a window function directly.)

2. Writefrom a blank page, then run it

   Return the two highest-paid employees in each department, including ties.

   Show answer

   Same as the solution, with rnk <= 2. RANK keeps ties: if two people shared second place, both would be included. Here Engineering gives Asha and Hana (Ben and Chen tie for third, so they are out), Sales gives Dara and Eli, and Marketing has only Fatima.

   sqlCopy

   WITH ranked AS (
     SELECT d.dept_name, e.name, e.salary,
            RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
     FROM employees e
     JOIN departments d ON d.dept_id = e.dept_id
   )
   SELECT dept_name, name, salary, rnk
   FROM ranked
   WHERE rnk <= 2
   ORDER BY dept_name, rnk, name;

   Try it

   5 rows

   dept_name	name	salary	rnk
   Engineering	Asha	95000	1
   Engineering	Hana	80000	2
   Marketing	Fatima	52000	1
   Sales	Dara	60000	1
   Sales	Eli	45000	2

3. Predictsay it before you run it

   What does the solution return if Hana's salary becomes 95000?

   Show answer

   Engineering now has two rows: Asha and Hana both earn 95,000, so both get rank 1. Marketing (Fatima) and Sales (Dara) are unchanged. 4 rows in all.

   The ORDER BY below adds name, so the two Engineering rows always come out in the same order.

   sqlCopy

   UPDATE employees SET salary = 95000 WHERE name = 'Hana';
   WITH ranked AS (
     SELECT d.dept_name, e.name, e.salary,
            RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk
     FROM employees e
     JOIN departments d ON d.dept_id = e.dept_id
   )
   SELECT dept_name, name, salary
   FROM ranked
   WHERE rnk = 1
   ORDER BY dept_name, name;

   Try it

   4 rows

   dept_name	name	salary
   Engineering	Asha	95000
   Engineering	Hana	95000
   Marketing	Fatima	52000
   Sales	Dara	60000

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Change the query so HR appears with NULL name and salary.

   Show answer

   Start from departments and LEFT JOIN employees, and partition by d.dept_id (not e.dept_id, which is NULL for HR). HR gets one row with NULL name and salary; it is the only row in its partition, so its rank is 1 and it survives the filter.

   sqlCopy

   WITH ranked AS (
     SELECT d.dept_name, e.name, e.salary,
            RANK() OVER (PARTITION BY d.dept_id ORDER BY e.salary DESC) AS rnk
     FROM departments d
     LEFT JOIN employees e ON e.dept_id = d.dept_id
   )
   SELECT dept_name, name, salary
   FROM ranked
   WHERE rnk = 1
   ORDER BY dept_name;

   Try it

   4 rows

   dept_name	name	salary
   Engineering	Asha	95000
   HR	NULL	NULL
   Marketing	Fatima	52000
   Sales	Dara	60000

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

This is top-N-per-group. I rank employees within each department with RANK partitioned by department and ordered by salary descending, in a CTE, then filter to rank one outside — window functions can't go in WHERE. I use RANK so tied top earners are all returned; if they wanted exactly one per department I'd switch to ROW_NUMBER with a tiebreaker. For "top three salaries" I'd use DENSE_RANK less than or equal to three.

Follow-up questions

Tick each check when you can do it without notes