Interview Drills · Nth highest · DENSE_RANK() vs ROW_NUMBER()
39. Third-highest salary per department
Concept: Nth highest · DENSE_RANK() vs ROW_NUMBER()show
'Nth highest salary' means the Nth distinct salary, so people on the same pay must not use up a place. DENSE_RANK() gives equal values the same rank with no gaps, so rank = N is exactly the Nth distinct value; ROW_NUMBER() counts people and RANK() skips numbers after a tie. A group with fewer than N distinct values has no answer at all.
Example (different data):
SELECT DISTINCT league, points
FROM (
SELECT league, points,
DENSE_RANK() OVER (PARTITION BY league ORDER BY points DESC) AS r
FROM teams
) AS t
WHERE r = 2;Your task
Classic interview question, with a twist: comp planning wants each department's third-highest salary level.
Return department and third_highest_salary (the third-highest distinct salary in that department), one row per department. Departments with fewer than three different salaries are left out. Sort by department.
Starting Postgres in your browser…
Checking your access…