Write a query using GROUP BY and HAVING to find departments with more than 10 employees earning above the department average salary.

Answer

This requires two comparisons at once - a department-level threshold (average salary) and a row-level one (each employee's own salary) - which is exactly why it needs a window function joined against a grouped aggregate, not GROUP BY alone.

Solution

SELECT department_id, COUNT(*) AS above_avg_count
FROM (
  SELECT e.department_id, e.salary,
         AVG(e.salary) OVER (PARTITION BY e.department_id) AS dept_avg_salary
  FROM Employees e
) x
WHERE salary > dept_avg_salary
GROUP BY department_id
HAVING COUNT(*) > 10;

Step by Step

1. The window function AVG(salary) OVER (PARTITION BY department_id) attaches each department's average salary to every one of its employee rows, without collapsing them.
2. The outer WHERE salary > dept_avg_salary keeps only employees earning above their own department's average.
3. GROUP BY department_id then counts how many such above-average employees exist per department.
4. HAVING COUNT(*) > 10 keeps only departments where that count exceeds 10.

Alternative Method: Subquery Instead of a Window Function

SELECT department_id, COUNT(*) AS above_avg_count
FROM Employees e
WHERE salary > (SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id)
GROUP BY department_id
HAVING COUNT(*) > 10;

This correlated-subquery version is logically identical but recalculates the department average once per outer row rather than once per department - typically slower on large tables, though easier to read for someone unfamiliar with window functions.

Key Exam Traps

• Filtering on a window function directly in WHERE is illegal SQL - WHERE salary > AVG(salary) OVER (...) in the same query level will error, since WHERE is evaluated before window functions. This is exactly why the average must be computed in a subquery/CTE first, then filtered in an outer query.
• GROUP BY alone cannot express "above average" - a plain GROUP BY department_id HAVING AVG(salary) < ... only filters departments as a whole, not individual employees within them, which is a different question entirely.
• HAVING vs WHERE placement - the HAVING COUNT(*) > 10 must apply to the final grouped count, not be confused with a row-level condition.