SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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.

Solution unlocks after 3 more tries

Starting Postgres in your browser…

query.sql
Output

Write a query and press Run Query (Ctrl/⌘ + Enter).