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.

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 the CTE version and 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.

Your task

Refactor the single-block query into CTEs in queries/04-cte.sql, one CTE per logical step, each named after what it produces - order_level, then with_country, then the aggregate. Keep order grain throughout so there is no fan-out. Run both the one-block and CTE versions and confirm they return identical numbers, with the United Kingdom leading at 95,706.22. 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;"

Check your understanding

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

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it runs your query, checks it against a rubric, and tells you what to fix or marks the lesson done.