Drill 1 of 12 in Window Functions
0

Window Functions · Window functions · DENSE_RANK()

1. 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).