Name your steps with CTEs

Why this lesson is here and not later

Most courses defer common table expressions to an advanced module. This one teaches them now, because agents write CTE-heavy SQL. A student who cannot read a chain of five CTEs cannot audit anything an agent produces. Reading comes before writing, and CTEs are what you will be reading.

A common table expression, written with WITH, is a named query you can refer to further down. Chaining them lets you build a result one labelled step at a time, so each step has a name that says what it produces.

Rewrite a query as named steps

Here is a question in the style of the last lesson - revenue by country, excluding cancelled orders - written the way most people first write it, as one block:

SELECT s.country_name, SUM(o.order_total) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.order_status IS DISTINCT FROM 'cancelled'
GROUP BY s.country_name
ORDER BY revenue DESC;

It works, but everything happens at once: the filter, the join, and the aggregate sit in one statement, and to review it you must hold all three in your head together. Now the same query split into steps: filter the orders, join to the store, then aggregate. Each CTE is named after its output.

WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;

This keeps order grain the whole way through, so order_total is summed once per order and there is no fan-out. stores has one clean row per store, so the join adds a country without duplicating anything.

Run both and confirm they return the same rows. A multi-line query is fine inside the quotes - paste it as is:

bruin query --connection duckdb-default \
  --description "revenue by country, CTE version" \
  --query "WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;"

The United Kingdom leads with 95,706.22. Then read each CTE name aloud: "order level, then with country, then revenue by country." Reading a query aloud is a cheap and genuinely effective review technique, because a name that does not match what the step does is easy to hear.

The order SQL actually runs in

You write SELECT first, but the database runs it late. The logical order is:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

This explains two things that confuse beginners. A column alias defined in SELECT cannot be used in WHERE, because WHERE runs first. And HAVING can filter on an aggregate, because it runs after GROUP BY, while WHERE cannot.

Ask your coding agent

SELECT p.category_name, SUM(oi.quantity * oi.net_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
GROUP BY p.category_name
ORDER BY revenue DESC;

Checkpoint

Without running anything, answer these:

• What does one row of each CTE in the query above represent?
• Why can HAVING filter on SUM(order_total) when WHERE cannot?
• Which step runs first, SELECT or FROM?