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

  1. Work out each department's average salary.
  2. Put that average next to each employee — a window function does both in one step.
  3. Keep rows where salary > the department average, filtering outside the CTE.
  4. Decide what to do with Gus, who has no department.

Edge cases to mention

Common mistakes

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.