Debugging an Inefficient Employee Salary Ranking Query
Fix a broken SQL query that attempts to rank employees by salary within each department but produces incorrect rankings and poor performance due to improper use of window functions and filters.
Challenge prompt
The current SQL query tries to rank employees by their salary within their department using ROW_NUMBER(), but it returns incorrect rankings and duplicates when employees have the same salary. Additionally, it filters on row numbers incorrectly causing performance issues. Your task is to identify and fix the bugs in the query so it correctly ranks employees by salary descending per department, handles ties with DENSE_RANK(), and optimizes the filtering to avoid unnecessary computations.
Guidance
- • Review the window function used: understand differences between ROW_NUMBER(), RANK(), and DENSE_RANK().
- • Check where the filtering on rank is applied; applying filters on window functions in the WHERE clause breaks the logic.
- • Consider moving the filtering to an outer query or use a CTE to improve readability and performance.
Hints
- • ROW_NUMBER() assigns unique ranks even if salaries are the same; consider using DENSE_RANK() or RANK() for correct tie handling.
- • Do not filter on window function results directly in the WHERE clause; use a subquery or CTE instead.
- • Use ORDER BY inside the window function correctly to rank salaries in descending order.
Starter code
SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary) AS salary_rank
FROM employees
WHERE salary_rank <= 3;Expected output
Should return top 3 employees by highest salary per department with correct ranking, including ties sharing the same rank.
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.