Active Headcount by Department — CTEs in SQL

The problem

The HR team at Helix Systems monitors staffing levels across departments.

Write a query to return the number of active employees in each department.

Assumptions:

The employees table has one row per employee with a department_id and an is_active flag.
Only active employees (where is_active is TRUE) count toward the headcount.
Each department_id with at least one active employee should appear once in the report.

Output:

One row per department, with columns department_id and headcount.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

WITH
  dept_headcount AS (
    SELECT
      department_id,
      COUNT(*) AS headcount
    FROM
      employees
    WHERE
      is_active = TRUE
    GROUP BY
      department_id
  )
SELECT
  department_id,
  headcount
FROM
  dept_headcount

The shape

The WHERE lives inside the WITH layer, so the filter runs before the grouping. Only active employees ever reach the COUNT(*), and the main query reads the already-filtered headcount per department straight out of the named layer.

Clause by clause

The WITH clause defines dept_headcount:

WITH dept_headcount AS (
  SELECT department_id, COUNT(*) AS headcount
  FROM employees
  WHERE is_active = TRUE
  GROUP BY department_id
)

The WHERE is_active = TRUE keeps only the rows that should count toward staffing; GROUP BY department_id then partitions those surviving rows by department, and COUNT(*) counts each partition. Department 1 ends up with 16 active employees, department 3 with 9, and so on.

SELECT department_id, headcount FROM dept_headcount is the main query. It reads the named layer by name and returns both of its columns. No additional filtering is needed because the active-only restriction already happened inside the layer.

The trap

The order of the clauses inside the layer matters. WHERE runs before GROUP BY, so WHERE is_active = TRUE restricts the input rows before any grouping occurs. Moving the same condition to the main query, where the layer has already aggregated, would have nothing to filter on; the is_active column does not exist in dept_headcount's output. The filter belongs where the column it references is still in scope, which is inside the layer.

You practiced applying a WHERE condition inside a WITH layer so the breakdown computes only over the qualifying records.

Return the number of active employees in each department

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.