Interview Drills · AVG() OVER · compare to group
24. Paid above department average
Concept: AVG() OVER · compare to groupshow
A window aggregate like AVG(salary) OVER (PARTITION BY department) puts the group's average on every row, so you can compare each row with its own group. A correlated subquery works too, but the window version reads more cleanly.
Example (different data):
SELECT name, score, AVG(score) OVER (PARTITION BY class) AS class_avg FROM students;
Your task
HR's pay-equity review wants everyone paid above their own department's average, shown next to that average.
Return department, name, salary and dept_avg for employees whose salary is above their department's average salary. Sort by department, salary (highest first), then name.
Starting Postgres in your browser…
Checking your access…