Department Average Comparison - The Classic Pattern

advanced

HR wants to identify employees earning above their department average for bonus consideration. Each department has different average, so we need row-by-row calculation.

-- Correlated subquery approach

SELECT

e1.employee_id,

e1.name,

e1.department,

e1.salary,

(SELECT ROUND(AVG(e2.salary), 0)

FROM employees e2

WHERE e2.department = e1.department) AS dept_avg,

e1.salary - (SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department = e1.department) AS diff_from_avg

FROM employees e1

WHERE e1.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department = e1.department -- Correlated: references e1

)

ORDER BY e1.department, e1.salary DESC;

-- Required index for performance:

CREATE INDEX idx_employees_dept_salary

ON employees(department, salary);

-- Performance: 10,000 employees

-- Without index: 8000ms (8 seconds)

-- With index: 450ms

-- Window function alternative: 95ms

employee_id | name | department | salary | dept_avg | diff_from_avg

5 | Alice | Engineering | 95000 | 92000 | 3000

12 | David | Engineering | 93000 | 92000 | 1000

23 | Carol | Sales | 82000 | 78000 | 4000

34 | Eve | Sales | 80000 | 78000 | 2000

45 | Frank | Marketing | 72000 | 68000 | 4000

Correlated subquery executes once per employee. For Alice in Engineering, it calculates Engineering average. For Carol in Sales, it calculates Sales average. Each employee gets their own department-specific comparison. The subquery appears 3 times in the query but executes once per row (so 3x per row = 30,000 total executions for 10,000 employees). Index on (department, salary) is critical - without it, each execution scans entire table.

All

With 10,000 employees: Correlated subquery with index: 450ms. Window function alternative: AVG(salary) OVER (PARTITION BY department) runs in 95ms (5x faster). For production, prefer window functions.

graph LR

A["Employee: Alice<br/>Dept: Engineering<br/>Salary: $95K"] --> B["Execute Subquery:<br/>AVG(salary)<br/>WHERE dept='Engineering'"]