All lessons

Module 5: Subqueries and CTEs · Lesson 5.4 · about 25 min

CTEs: WITH, step by step

A CTE gives a subquery a name at the top of your query, so you can build an answer in readable steps. Rewrite nested queries with WITH, chain several steps, and work out each store's share of Northbay's revenue.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 5 · about 25 minutes · read first: 5.1. You’ll be able to write a query as a series of named steps with WITH, check each step on its own, and turn a hard question into a few easy ones.

A CTE gives a step of your query a name with WITH, so a hard question becomes a list of easy steps you read from top to bottom.

In everyday terms: a recipe written as numbered steps: “1. make the dough, 2. make the sauce, 3. put the sauce on the dough”. Each step can use what the earlier steps made.

1. Why this matters

Real analyst queries are often 50 to 200 lines long. Written as subqueries inside subqueries, they become impossible to read or fix. A CTE (common table expression) lets you write the same logic as a list of named steps, top to bottom. Most BI teams write nearly all their SQL this way, and code reviewers expect it.

2. Naming a step with WITH

A CTE starts with WITH name AS (query), followed by the main query, which uses name like a table:

-- try
WITH big_orders AS (
  SELECT order_id, customer_id, amount
  FROM mini_orders
  WHERE amount >= 45
)
SELECT c.name, b.order_id, b.amount
FROM big_orders b
JOIN mini_customers c ON c.id = b.customer_id

Press Show how it runs: big_orders is built first (four orders of $45 or more), then joined to the customers. Order 106 has no customer, so the inner join drops it and three rows are left. The CTE exists only while this one statement runs; nothing is saved in the database.

3. The same query, nested and with WITH

Here is the average-orders-per-customer question from Lesson 5.1, rewritten. Compare how it reads:

-- try
WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS orders
  FROM orders
  WHERE customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT ROUND(AVG(orders), 2) AS avg_orders_per_customer,
       MAX(orders) AS most_orders
FROM per_customer

Same answer, 5.97 and 14, but now it reads in the order it happens: first count per customer, then average. When a number looks wrong, you can run just the CTE’s SELECT on its own to inspect the middle step. That habit alone saves hours.

4. Several steps, each using the last

One WITH, then CTEs separated by commas. Each can use the ones above it.

Question: how many customers fall into each spending band, counting only completed orders?

-- try
WITH line_values AS (
  SELECT o.customer_id,
         oi.quantity * oi.unit_price * (1 - oi.discount) AS value
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
    AND o.customer_id IS NOT NULL
),
spend AS (
  SELECT customer_id, SUM(value) AS total
  FROM line_values
  GROUP BY customer_id
),
banded AS (
  SELECT customer_id,
         CASE WHEN total >= 500 THEN 'High'
              WHEN total >= 200 THEN 'Medium'
              ELSE 'Low' END AS band
  FROM spend
)
SELECT band, COUNT(*) AS customers
FROM banded
GROUP BY band
ORDER BY customers DESC

Low 361, Medium 223, High 14. Each step does one job: pick the lines, add them up per customer, label each customer, count the labels. Good CTE names say what one row is: spend is “one row per customer with their total”.

Maths and principle

Principle: a CTE is a named intermediate result. Naming does not change the answer; it only makes the composition of steps visible.

Equation: WITH a AS (Q₁), b AS (Q₂(a)) SELECT Q₃(b) is the same as Q₃(Q₂(Q₁)). It is the “let” of maths: let a = Q₁, let b = Q₂(a), answer = Q₃(b). Because a CTE can be used more than once, it also avoids writing Q₁ twice, which a nested query would need.

Analogy: showing your working in a maths exam. Writing “let x = 3 + 4” and then “x² = 49” gives the same answer as (3 + 4)² = 49, but a marker can check each line, and so can you.

Try it: in the banding query, what is the grain (one row is …) of spend? (One customer.) Of line_values? (One order line.)

CTE (common table expression)
A named query written before the main query: WITH name AS (SELECT ...).
WITH
The word that starts the list of CTEs. One WITH, then CTEs separated by commas.
Main query
The SELECT after the last CTE. It uses the CTE names like tables.
Check a step
Run one CTE's SELECT on its own to see the middle result.
Physical meaning
Each CTE is a temporary result that exists only while this one statement runs. Nothing is stored in the database, and the result is the same as the nested-subquery version.

“A CTE saves a table I can use in my next query.” It lasts for one statement only. Run a second query and the name is gone. To keep a result, create a table or a view.

Watch it happen, one step at a time (any live box’s Show how it runs button animates the real query on Northbay):

Start. mini_orders.
order_idcustomer_idamount
101145
10215
103220
104390
105345
106960
Step 1: spend. WITH spend AS (SELECT customer_id, SUM(amount) AS total ... GROUP BY customer_id)
customer_idtotal
150
220
3135
960
Step 2: banded. uses spend: CASE WHEN total >= 50 THEN 'High' ELSE 'Low' END
customer_idband
1High
2Low
3High
9High
Main query. SELECT band, COUNT(*) FROM banded GROUP BY band
bandcustomers
High3
Low1
Each step did one job.

5. Worked example: each store’s share of 2025 revenue

Question: what share of Northbay’s completed 2025 revenue did each store bring in?

-- try
WITH lines_2025 AS (
  SELECT o.store_id,
         oi.quantity * oi.unit_price * (1 - oi.discount) AS value
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
    AND o.order_date LIKE '2025%'
),
by_store AS (
  SELECT store_id, SUM(value) AS revenue
  FROM lines_2025
  GROUP BY store_id
),
chain AS (
  SELECT SUM(revenue) AS total FROM by_store
)
SELECT s.store_name,
       ROUND(b.revenue, 2) AS revenue,
       ROUND(100.0 * b.revenue / c.total, 1) AS pct_of_chain
FROM by_store b
JOIN stores s ON s.store_id = b.store_id
CROSS JOIN chain c
ORDER BY pct_of_chain DESC
  1. lines_2025: every completed 2025 order line with its store and value.
  2. by_store: one row per store with its revenue.
  3. chain: one row, the total: $72,594.52.
  4. The final SELECT puts the total next to every store (CROSS JOIN with a one-row table just adds its columns to every row) and divides.

Northbay Online leads with 28.8% ($20,883.03), then Bytown 8.1% and River Valley 7.8%. Note 100.0, not 100: in SQLite and SQL Server, dividing two whole numbers throws away the decimals.

6. Practice

-- exercise: Rewrite with a CTE called per_order: first make one row per order with its total, then show the average order total rounded to 2 decimal places.
-- hint: WITH per_order AS (SELECT order_id, SUM(quantity * unit_price * (1 - discount)) AS total FROM order_items GROUP BY order_id) SELECT ROUND(AVG(total), 2) FROM per_order.
SELECT ROUND(AVG(quantity * unit_price * (1 - discount)), 2) AS avg_value
FROM order_items
-- answer:
WITH per_order AS (
  SELECT order_id, SUM(quantity * unit_price * (1 - discount)) AS total
  FROM order_items
  GROUP BY order_id
)
SELECT ROUND(AVG(total), 2) AS avg_order_total
FROM per_order
-- exercise: Using two CTEs, show each department's 2025 revenue (completed orders) and its share of the 2025 total as a percentage rounded to 1 decimal place.
-- hint: First CTE: revenue per department (order_items → orders → products → categories, filtered to completed 2025). Second CTE: the total. Then CROSS JOIN and divide with 100.0.
WITH dept AS (
  SELECT c.department,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM order_items oi
  JOIN orders o     ON o.order_id = oi.order_id
  JOIN products p   ON p.product_id = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  WHERE o.status = 'completed' AND o.order_date LIKE '2025%'
  GROUP BY c.department
)
SELECT department, ROUND(revenue, 2) AS revenue
FROM dept
-- answer:
WITH dept AS (
  SELECT c.department,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM order_items oi
  JOIN orders o     ON o.order_id = oi.order_id
  JOIN products p   ON p.product_id = oi.product_id
  JOIN categories c ON c.category_id = p.category_id
  WHERE o.status = 'completed' AND o.order_date LIKE '2025%'
  GROUP BY c.department
),
total AS (
  SELECT SUM(revenue) AS t FROM dept
)
SELECT d.department, ROUND(d.revenue, 2) AS revenue,
       ROUND(100.0 * d.revenue / total.t, 1) AS pct
FROM dept d
CROSS JOIN total
-- exercise: Find the physical stores (leave out store 12, online) whose 2025 order count (all statuses) was above the average physical store's. Show store_id and the count.
-- hint: CTE counts: store_id, COUNT(*) AS n for 2025, with store_id <> 12. Then SELECT from counts WHERE n > (SELECT AVG(n) FROM counts).
WITH counts AS (
  SELECT store_id, COUNT(*) AS n
  FROM orders
  WHERE order_date LIKE '2025%' AND store_id <> 12
  GROUP BY store_id
)
SELECT store_id, n
FROM counts
-- answer:
WITH counts AS (
  SELECT store_id, COUNT(*) AS n
  FROM orders
  WHERE order_date LIKE '2025%' AND store_id <> 12
  GROUP BY store_id
)
SELECT store_id, n
FROM counts
WHERE n > (SELECT AVG(n) FROM counts)

Try it on your own data: paste a long nested query from work into [[sql-playground]] with a sample of the data and untangle it into named CTE steps.

  • WITH name AS (query) names a step; the main query uses it like a table.
  • Several CTEs go after one WITH, separated by commas; each can use the ones above.
  • A CTE lasts only for one statement.
  • Name each CTE after what one row means, and run steps on their own to check them.

New words: CTE (common table expression), WITH, CROSS JOIN.

Check yourself

1What does WITH big AS (SELECT ...) do?
2How do you write two CTEs in one query?
3Why do analysts prefer CTEs to deeply nested subqueries?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.