CTEs & Window Functions SQL practice problemEasyVerified answer

Rank Employees by Salary (ROW_NUMBER)

Return each employee with their row number rank within their department by salary.

  • CTEs
  • Window functions
  • Joins
  • Sorting

Interview brief

Understand the request

People analytics analyst The salary ranking board assigns each employee a rank within their department using ROW_NUMBER.

Assign a unique row number to every employee ordered by salary descending (ties broken by employee_id). Return first_name, last_name, salary, department_name, row_num — ordered by row_num.

Return

  • Return employee_name, department_id, salary, and dept_rank.

Constraints

  • Use ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC).

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

ROW_NUMBER() always produces 1,2,3,… with no ties — even when the ORDER BY values are equal.

Hint 2

Add a tie-breaker (employee_id) inside OVER(ORDER BY …) so the numbering is deterministic.

Hint 3

Window functions do NOT collapse rows — every input row keeps its identity (unlike GROUP BY).

Verified SQL answer

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

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

Why this works

ROW_NUMBER is the simplest window function: it numbers rows within an ordering. Because it never ties, it's the backbone of 'top-N per group' and deduplication patterns (see Q11–Q13).

Expected result

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

first_namelast_namesalarydepartment_namerow_num
JenniferKing250000Executive1
MichaelScott180000Engineering2
SarahJohnson175000Sales3
DavidMartinez130000Engineering4
EmilyChen125000Engineering5
JamesTaylor110000Sales6
JessicaMoore105000Finance7
RobertWilliams95000Engineering8
PatriciaDavis95000Human Resources9
LisaAnderson90000Engineering10

Previewing 10 of 15 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: