SQL CTE: Build a Report in Readable Stages

The practice task

A training coordinator wants departments with more than one completed course registration. Count completed registrations first, then filter that result. The sample keeps cancelled registrations in the source so a missing status filter produces an observable mistake.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE registrations(dept TEXT, status TEXT);
INSERT INTO registrations VALUES ('Support','completed'),('Support','completed'),('Support','cancelled'),('Design','completed'),('Design','cancelled');

WITH completed AS (
  SELECT dept, COUNT(*) AS total
  FROM registrations
  WHERE status = 'completed'
  GROUP BY dept
), qualifying AS (
  SELECT dept, total FROM completed WHERE total > 1
)
SELECT dept, total FROM qualifying ORDER BY dept;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The first CTE reduces five registrations to two department totals: Support has two completions and Design has one. The second keeps only the department above the threshold. Reading those stages separately makes the population and the threshold easy to inspect.

Filtering status after aggregation would lose the distinction between completed and cancelled registrations unless status were also retained as a grouping key. Put a row condition where the relevant row information still exists. The result is one Support row, not three registrations.

For a small query, a derived table or HAVING condition may be simpler. Choose a CTE when the names explain a real sequence of work. Compare an execution plan before claiming a performance benefit; a cleaner explanation and a faster query are different outcomes.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• The CTE name lasts for this statement, not the next statement.
• Do not give two stages vague names such as data1 and data2.
• Count the intended population before applying a group threshold.