Schema and sample data
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Ava Torres | Engineering | 60000.00 |
| 2 | Ben Ncube | Engineering | 80000.00 |
| 3 | Cara Singh | Engineering | 100000.00 |
| 4 | Deng Liu | Sales | 40000.00 |
| 5 | Emma Rossi | Sales | 60000.00 |
| 6 | Finn Doyle | Support | 45000.00 |
| 7 | Gina Alvarez | Support | 55000.00 |
| 8 | Hana Kim | Support | 50000.00 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30),
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO Employees (emp_id, name, department, salary) VALUES
(1, 'Ava Torres', 'Engineering', 60000.00),
(2, 'Ben Ncube', 'Engineering', 80000.00),
(3, 'Cara Singh', 'Engineering', 100000.00),
(4, 'Deng Liu', 'Sales', 40000.00),
(5, 'Emma Rossi', 'Sales', 60000.00),
(6, 'Finn Doyle', 'Support', 45000.00),
(7, 'Gina Alvarez', 'Support', 55000.00),
(8, 'Hana Kim', 'Support', 50000.00);From the Employees table, return the name, department and salary of every employee whose salary is strictly greater than the average salary of their own department. Sort the result by department ascending, then by salary from highest to lowest.
Expected result
| name | department | salary |
|---|---|---|
| Cara Singh | Engineering | 100000.00 |
| Emma Rossi | Sales | 60000.00 |
| Gina Alvarez | Support | 55000.00 |
Show hint
The threshold is different for each row, because it depends on the employee's own department. A correlated subquery lets the inner AVG(salary) reference the outer row: filter the inner query so it only averages rows in the same department as the current employee.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT e.name, e.department, e.salary
FROM Employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM Employees AS e2
WHERE e2.department = e.department
)
ORDER BY e.department, e.salary DESC;The inner query is a correlated subquery: it mentions e.department from the outer query, so it cannot be run once on its own. Instead it is re-evaluated for each outer row, computing the average salary of just that row's department. The three department averages here are Engineering 80000 (from 60000, 80000, 100000), Sales 50000 (from 40000, 60000) and Support 50000 (from 45000, 55000, 50000).
Each employee is then kept only when e.salary is strictly greater than that department average. Cara Singh (100000 > 80000), Emma Rossi (60000 > 50000) and Gina Alvarez (55000 > 50000) survive. Note that an employee sitting exactly on the average, such as anyone matching their department mean, is excluded because the comparison is > and not >=.
A cleaner alternative avoids re-running the subquery per row by using a window function. Wrap the table in a subquery or CTE that computes AVG(salary) OVER (PARTITION BY department) as a per-row department average, then filter WHERE salary > dept_avg in the outer query. You cannot reference a window function directly in WHERE, which is why it needs the extra layer. See AVG for the windowed form. Both approaches return the same rows; the window version usually scans the table once instead of once per employee.