Active Employee Counts via CTE — Query Performance in SQL

The problem

Scenario: Helix Systems' HR operations team needs a current headcount of active employees broken out by department.

Task: Write a query to return each department_id and the count of active employees assigned to it.

Assumptions:

The employees table holds one row per employee, with department_id recording their assignment and is_active flagging current activity.
An active employee has is_active equal to TRUE.
The result covers only active employees.

Output:

One row per department with at least one active employee.
Columns in this order: department_id, active_count.

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
  active_emps AS (
    SELECT
      department_id
    FROM
      employees
    WHERE
      is_active = TRUE
  )
SELECT
  department_id,
  COUNT(*) AS active_count
FROM
  active_emps
GROUP BY
  department_id

The shape

Two operations have to happen in order: filter to active employees, then count per department. A CTE names the filtered set first, and the main query does the per-department count on that already-narrow input.

Clause by clause

The CTE active_emps reads employees with WHERE is_active = TRUE and projects only department_id. The filter runs first, so every row downstream is already an active employee.
SELECT department_id, COUNT(*) AS active_count reads the CTE and counts rows per group.
FROM active_emps references the CTE by name. From the main query's perspective, it behaves exactly like a table.
GROUP BY department_id produces one row per department that has at least one active employee. Departments with zero active employees do not appear, because they had no rows in active_emps to begin with.

Why this and not filter-in-the-main-query

Pushing the same filter into the main query — WHERE is_active = TRUE directly on employees with the GROUP BY after it — produces the same result on this data, and on PostgreSQL 12+ the planner often inlines the CTE into exactly that shape anyway. The CTE here is doing pedagogical work: the row-narrowing step is named, and the per-department count reads as a separate phase. Structurally separating the filter from the aggregation is the pattern this problem is practicing.

You practiced narrowing rows to active employees first, then counting per department — a structural shape that keeps the count over an already-trimmed set.

Return each `department_id` and the count of active employees assigned to it

The shape

Clause by clause

Why this and not filter-in-the-main-query

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