lesson 31 Dates, strings & subqueries · intermediate

Readable queries with CTEs

Break a query into named, readable steps.

the idea

WITH names a temporary query result for this statement. Common table expressions help you separate a complicated question into understandable stages. They do not create permanent tables.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

WITH totals AS (
  SELECT customer_id, SUM(quantity) AS units
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, units
FROM totals
WHERE units > 3;

Watch for: A CTE exists only for the statement that follows it.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Name an intermediate total. WITH defines a temporary result you can read like a table. Here each customer gets a units total before filtering.
step-1.sql
sql›
```
SELECT customer_id, SUM(quantity) AS units
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
```
2
Filter the named CTE. The outer query reads totals as if it were a table. CTEs make multi-step logic easier to follow than nested subqueries.
step-2.sql
sql›
```
WITH totals AS (
  SELECT customer_id, SUM(quantity) AS units
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, units
FROM totals
WHERE units > 3;
```