Employees paid above their department's average
List employees whose salary is above the average salary of their own department, showing that average next to them.
What it tests
Comparing a row with an aggregate of its own group — three valid ways (window function, join to a grouped subquery, correlated subquery) and knowing when each fits.
The pattern
Compare a row with its group. "Above the average for their…", "more than their team's…", "compared with others in the same…".
How to approach it
- Work out each department's average salary.
- Put that average next to each employee — a window function does both in one step.
- Keep rows where salary > the department average, filtering outside the CTE.
- Decide what to do with Gus, who has no department.
Edge cases to mention
- An employee with no department: the window version groups NULLs together; the join version drops them. Ask which is wanted.
- A department of one person can never be above its own average.
- "Above" means strictly greater; say so.
Common mistakes
WHERE salary > AVG(salary)— an aggregate in WHERE is not allowed, and it would be the company average anyway.- Comparing with the company average when the question says "their department".
- Filtering on the window function in the same SELECT's WHERE.
Not true: WHERE salary > AVG(salary) compares each salary with the average of that person's department.
In fact: An aggregate is not allowed in WHERE, and with no grouping it would be the company average anyway. Work out the department average with a window function or a grouped subquery.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.