Interview Drills · Window functions · DENSE_RANK()
21. Top 3 earners per department
Concept: Window functions · DENSE_RANK()show
Window functions compute a value for each row from a 'window' of related rows, without collapsing them like GROUP BY. PARTITION BY restarts the calculation per group. DENSE_RANK gives ties the same rank with no gaps (1, 2, 2, 3); RANK leaves gaps (1, 2, 2, 4); ROW_NUMBER never ties.
Example (different data):
SELECT name, score, DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rnk FROM players;
Your task
Classic FAANG screen: comp review needs the top 3 distinct salary levels in each department. People on the same salary share a rank.
Return department, name, salary and salary_rank (DENSE_RANK by salary, highest first, within each department) for everyone with salary_rank <= 3. Sort by department, salary_rank, name.
Starting Postgres in your browser…
Write a query and press Run Query (Ctrl/⌘ + Enter).