CTEs & Window Functions SQL practice problemMediumVerified answer

RANK vs DENSE_RANK Within Departments

Return each employee with their RANK and DENSE_RANK by salary within their department.

  • CTEs
  • Window functions
  • Joins
  • Sorting

Interview brief

Understand the request

Compensation ranking lead The pay hierarchy report uses both RANK and DENSE_RANK to show salary positioning, handling ties differently.

For each department, show every employee's salary RANK and DENSE_RANK (highest salary = rank 1). Return department_name, first_name, last_name, salary, salary_rank, salary_dense_rank — ordered by department_name, salary DESC, employee_id.

Return

  • Return employee_name, department_id, salary, rank_position, and dense_rank_position.

Constraints

  • Use both RANK() and DENSE_RANK() with PARTITION BY department 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

RANK skips numbers after a tie (1,1,3); DENSE_RANK keeps them consecutive (1,1,2).

Hint 2

Both differ from ROW_NUMBER, which never ties (1,2,3) even on equal values.

Hint 3

PARTITION BY department_id restarts the ranking per department.

Verified SQL answer

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

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

Why this works

RANK, DENSE_RANK, and ROW_NUMBER are the three ranking windows. The difference is purely tie behavior: ROW_NUMBER (always unique), RANK (gaps after ties), DENSE_RANK (no gaps). Pick based on whether downstream logic expects gaps.

Expected result

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

department_namefirst_namelast_namesalarysalary_ranksalary_dense_rank
EngineeringMichaelScott18000011
EngineeringDavidMartinez13000022
EngineeringEmilyChen12500033
EngineeringRobertWilliams9500044
EngineeringLisaAnderson9000055
EngineeringAmandaWhite7500066
ExecutiveJenniferKing25000011
FinanceJessicaMoore10500011
FinanceDanielLee7000022
Human ResourcesPatriciaDavis9500011

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: