CTEs & Window Functions SQL practice problemMediumVerified answer

Top Earner Per Department (ROW_NUMBER = 1)

Find the single highest-paid employee in each department (ties broken by employee_id). Use ROW_NUMBER in a CTE and filter to rn = 1. Return department_name, first_name, last_name, salary — ordered by department_name.

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Filtering

Interview brief

Understand the request

Find the single highest-paid employee in each department (ties broken by employee_id). Use ROW_NUMBER in a CTE and filter to rn = 1. Return department_name, first_name, last_name, salary — ordered by department_name.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

You cannot filter a window function in WHERE directly — wrap it in a CTE/subquery, then filter rn = 1.

Hint 2

ROW_NUMBER (not RANK) guarantees exactly one row per group even on salary ties.

Hint 3

The tie-breaker (employee_id) decides who wins when two share the top salary.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH ranked AS (SELECT e.department_id, d.department_name, e.first_name, e.last_name, e.salary, ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC, e.employee_id) AS rn FROM employees e INNER JOIN departments d ON e.department_id = d.department_id) SELECT department_name, first_name, last_name, salary FROM ranked WHERE rn = 1 ORDER BY department_name;

Why this works

Top-1-per-group is THE canonical window question. ROW_NUMBER + outer filter beats the old correlated-MAX subquery: one pass, ties handled deterministically, and it generalizes to top-N (Q12).

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_namefirst_namelast_namesalary
EngineeringMichaelScott180000
ExecutiveJenniferKing250000
FinanceJessicaMoore105000
Human ResourcesPatriciaDavis95000
SalesSarahJohnson175000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: