Unit 06.01: CTEs: naming each step so it can be checked

Unit ID: SQL-M06-U02 - Estimated active time: 13-16 minutes Objective: refactor nested logic into named stages a reviewer can read and test independently.

A CTE gives a step a name

WITH completed AS (
  SELECT * FROM orders WHERE status = 'completed'
),
per_country AS (
  SELECT c.country,
         COUNT(*)           AS orders,
         SUM(o.order_total) AS revenue
  FROM completed o
  JOIN customers c ON c.customer_id = o.customer_id
  GROUP BY c.country
)
SELECT * FROM per_country ORDER BY revenue DESC;

Compare with the same logic as nested subqueries: the reader would have to work inside-out to understand it. Here the query reads top to bottom in the order the work happens.

The real benefit is testability

Any CTE can be run on its own by changing the final SELECT:

WITH completed AS (
  SELECT * FROM orders WHERE status = 'completed'
)
SELECT COUNT(*) FROM completed;
-- 988

That turns each stage from Unit 06.01 into something permanently re-checkable, rather than a step you verified once and then buried.

CTEs and performance

A CTE is usually a naming device, not a performance one. Modern engines including DuckDB and PostgreSQL generally inline them, so the plan is comparable to the equivalent subquery. Write CTEs for clarity and measure if performance matters; do not assume they are faster or slower.

Non-example

A single-step query does not need one:

-- Clear as it is
SELECT COUNT(*) FROM orders WHERE status = 'pending';   -- 12

-- Ceremony for nothing
WITH p AS (SELECT * FROM orders WHERE status = 'pending')
SELECT COUNT(*) FROM p;

Reach for a CTE when there is a genuine stage worth naming.

Practice

Rewrite this using CTEs so each stage can be run independently, then run each stage.

SELECT country, orders FROM (
  SELECT c.country, COUNT(*) AS orders
  FROM orders o JOIN customers c ON c.customer_id = o.customer_id
  GROUP BY c.country
) t WHERE orders > 100;

WITH joined AS (
  SELECT c.country, o.order_id
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
),
counted AS (
  SELECT country, COUNT(*) AS orders
  FROM joined
  GROUP BY country
)
SELECT * FROM counted WHERE orders > 100 ORDER BY orders DESC;

Takeaway

Name each stage with a CTE so the query reads in execution order and every step stays independently runnable.

---

Mark complete