SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…