SQL Subqueries SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Find Departments Larger Than the Average Department

Build department headcounts in a derived table, average them in a scalar subquery, and filter the outer grouped result.

  • Joins
  • Subqueries
  • Aggregation
  • HAVING
  • Sorting

Exercise brief

Understand the request

Workforce planning manager Capacity planning needs departments whose headcount is above the average populated-department headcount.

Find departments whose employee count exceeds the average department size (computed across departments that actually have employees). Return department_id, department_name, employee_count — ordered by employee_count DESC, department_id.

Return

  • Return department_id, department_name, employee_count in this exact left-to-right order.

Constraints

  • Use a FROM-subquery (derived table) for the per-department counts.
  • Compare grouped headcount in HAVING; do not hard-code the threshold.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

employees

  • employee_idINTEGER
  • department_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

HAVING is the right place to filter aggregates — WHERE happens BEFORE GROUP BY.

Hint 2

The inner FROM-subquery (a derived table) computes per-dept counts; AVG over that gives the threshold.

Hint 3

Derived tables MUST be aliased — every engine requires `... ) sub`.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d INNER JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name HAVING COUNT(e.employee_id) > (SELECT AVG(emp_count) FROM (SELECT COUNT(employee_id) AS emp_count FROM employees GROUP BY department_id) sub) ORDER BY employee_count DESC, d.department_id;

Why this works

Derived tables (subquery in FROM) let you aggregate, then aggregate-of-aggregates. They're indispensable when you need a value computed at one grain to filter rows at another grain.

Success check

Only departments above the computed populated-department average appear at department grain.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_iddepartment_nameemployee_count
10IT4

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.