SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…