Solving tips
- A correlated subquery references a column from the outer query, so it is re-evaluated once per outer row — here, once per employee, against that employee's own department.
- Alias the outer and inner tables differently (e.g. `e` and `e2`) so the correlation condition `e2.department_id = e.department_id` is unambiguous.
- The comparison is strictly greater than the group average, so an employee sitting exactly at their department average is excluded.
Find every employee who earns strictly more than the average salary of their own department.
Schema
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer NOT NULL,
salary integer NOT NULL
);
Sample data — employees:
| id | name | department_id | salary |
|---|
| 1 | Alice | 10 | 90000 |
| 2 | Bob | 10 | 60000 |
| 3 | Carol | 20 | 100000 |
| 4 | Dave | 20 | 55000 |
| 5 | Eve | 30 | 120000 |
| 6 | Frank | 30 | 55000 |
| 7 | Grace | 20 | 65000 |
Task
Return the name, department_id, and salary of every employee whose salary is strictly greater than the average salary of the department they belong to.
Order by department_id ascending, then by salary descending. Expected columns: name, department_id, salary.
Expected output
Department averages: dept 10 = 75000, dept 20 = (100000 + 55000 + 65000) / 3 ≈ 73333.33, dept 30 = 87500.
| name | department_id | salary |
|---|
| Alice | 10 | 90000 |
| Carol | 20 | 100000 |
| Eve | 30 | 120000 |
Approach
Each employee must be compared against a threshold that depends on their own department, so a single non-correlated scalar value will not do. Use a correlated subquery: for the current outer row e, compute the average salary over only the rows sharing e.department_id. The correlation link is the predicate e2.department_id = e.department_id, which ties the inner aggregate to the outer employee.
Query
SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id, e.salary DESC;
Walkthrough
For Alice (dept 10) the inner query averages salaries in dept 10, (90000 + 60000) / 2 = 75000; 90000 > 75000, so Alice qualifies while Bob (60000) does not. For dept 20 the average of Carol, Dave, and Grace is 220000 / 3 ≈ 73333.33; only Carol (100000) clears it. For dept 30 the average is (120000 + 55000) / 2 = 87500; Eve (120000) qualifies, Frank (55000) does not. Ordering by department_id then salary DESC yields Alice, Carol, Eve.
Complexity & notes
Conceptually the inner aggregate runs once per outer row, so a naive plan is O(n) scans over the group — quadratic in the worst case. An index on department_id helps, and many engines rewrite this into a window function (AVG(salary) OVER (PARTITION BY department_id)) or a join to a grouped subquery, both of which scan once. Pitfalls: a department with a single member averages to that member’s own salary, so they can never be strictly greater and are correctly excluded; NULL salaries are ignored by AVG but would still be compared, and salary > NULL is never true.