CTEs & Window Functions SQL practice problemMediumVerified answer

Top 3 Earners Per Department (ROW_NUMBER ≤ 3)

List the three highest-paid employees in each department, with their in-department rank. Return department_name, first_name, last_name, salary, dept_rank — ordered by department_name, dept_rank.

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Filtering

Interview brief

Understand the request

List the three highest-paid employees in each department, with their in-department rank. Return department_name, first_name, last_name, salary, dept_rank — ordered by department_name, dept_rank.

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

Change the filter from rn = 1 to rn <= N for 'top N per group'.

Hint 2

Use ROW_NUMBER for exactly N rows; use RANK/DENSE_RANK if you want to INCLUDE ties at the cutoff.

Hint 3

Departments with fewer than 3 employees simply return all of theirs.

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 dept_rank FROM employees e INNER JOIN departments d ON e.department_id = d.department_id) SELECT department_name, first_name, last_name, salary, dept_rank FROM ranked WHERE dept_rank <= 3 ORDER BY department_name, dept_rank;

Why this works

Top-N-per-group generalizes top-1: just widen the filter. The choice of ROW_NUMBER vs RANK/DENSE_RANK decides whether ties at the boundary push you over N rows. ROW_NUMBER caps it at exactly N.

Expected result

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

department_namefirst_namelast_namesalarydept_rank
EngineeringMichaelScott1800001
EngineeringDavidMartinez1300002
EngineeringEmilyChen1250003
ExecutiveJenniferKing2500001
FinanceJessicaMoore1050001
FinanceDanielLee700002
Human ResourcesPatriciaDavis950001
Human ResourcesChristopherWilson650002
SalesSarahJohnson1750001
SalesJamesTaylor1100002

Previewing 10 of 11 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: