Interview Question Bank · DENSE_RANK · PARTITION BY · NULLs sort first
8. Top three salary levels per department
MediumGeneralQuestion #200
Concept: DENSE_RANK · PARTITION BY · NULLs sort firstshow
DENSE_RANK() OVER (PARTITION BY group ORDER BY value DESC) numbers distinct values 1, 2, 3 within each group, giving ties the same number with no gaps. Filter on it in an outer query. Watch for NULLs: Postgres sorts NULL as larger than any value, so in DESC order a NULL takes rank 1 unless you drop it or write NULLS LAST.
Example (different data):
SELECT team, player, points
FROM (
SELECT team, player, points,
DENSE_RANK() OVER (PARTITION BY team ORDER BY points DESC NULLS LAST) AS lvl
FROM scores
) s
WHERE lvl <= 2;Your task
Mosswood Studio is reviewing pay and wants everyone earning one of the three highest salaries in their department. People on the same salary share a level, and new hires with a salary still waiting for approval are left out.
Return department (the department's name), name and salary for every staff member whose salary is among the three highest distinct salaries in their department. Ignore staff with a NULL salary. Sort by department, salary descending, then name.
Starting Postgres in your browser…
Checking your access…