The highest-paid employee in each department
For each department, return the employee (or employees, if tied) with the highest salary. Show the department name, the employee's name and their salary.
What it 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".
How to approach it
- Ask: if two people tie for top salary in a department, show both? (Yes here.)
- Rank employees within each department by salary, highest first:
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC). - RANK keeps ties together; ROW_NUMBER would pick one at random.
- Do the ranking in a CTE, then keep rank 1 outside it, and join departments for the name.
Edge cases to mention
- Ties for the top: RANK (or DENSE_RANK) returns all of them; ROW_NUMBER returns one.
- Departments with no employees (HR): not in the result. Ask whether they should appear with NULLs (then LEFT JOIN from departments).
- Employees with no department (Gus): dropped by the inner join to departments.
Common mistakes
- Putting the window function in WHERE.
GROUP BY dept_idwithMAX(salary)andnamein SELECT — the name isn't guaranteed to belong to the max.- Using ROW_NUMBER when ties should all be returned.
Not true: SELECT dept_id, name, MAX(salary) … GROUP BY dept_id returns the name of each department's top earner.
In fact: The name is not tied to the maximum: most databases reject the query, and the rest may show any name from the group. Rank the rows with a window function instead.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.