Common Table Expressions
WITH makes queries readable.
Subqueries are powerful, but they get messy. A subquery inside a subquery, inside another one, is very hard to read. There's a tidier way. You give each step a name, and write the steps in order, from top to bottom. It's called a common table expression, or CTE.

- Nested subqueries get messy
- Name each step
- Read top to bottom
Your first CTE
Start with the word with, then a name, then as, and the query in brackets. Here, monthly is a little table of orders per month. Then, below it, the main query uses monthly, just like a normal table. Run it. Helen gets about sixty seven orders in an average month. Her best month had one hundred and twenty.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY month
)
SELECT ROUND(AVG(orders), 1) AS avg_per_month,
MAX(orders) AS best_month
FROM monthly;โโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ avg_per_month โ best_month โ โโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโค โ 66.6 โ 120 โ โโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโ
Use it twice
ACTE can be used more than once. Here, monthly appears in the from, and again in the subquery, to find months above average. Run it. August twenty twenty five was the busiest, then December, then May. All the best months are in twenty twenty five.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY month
)
SELECT month, orders
FROM monthly
WHERE orders > (SELECT AVG(orders) FROM monthly)
ORDER BY orders DESC
LIMIT 5;โโโโโโโโโโโฌโโโโโโโโโ โ month โ orders โ โโโโโโโโโโโผโโโโโโโโโค โ 2025-08 โ 120 โ โ 2025-12 โ 104 โ โ 2025-05 โ 103 โ โ 2025-09 โ 93 โ โ 2025-10 โ 93 โ โโโโโโโโโโโดโโโโโโโโโ
Subquery versus CTE
Both do the same job. But a CTE reads from top to bottom, has a clear name, and can be reused. Most professionals prefer CTEs for anything longer than a couple of lines.
Subquery
- Nested inside
- No name
- Read inside out
CTE (WITH)
- Written first
- Has a name
- Read top to bottom
Summary
With, a name, and a query in brackets. That's a CTE. It turns a messy query into simple, named steps. Next, chaining several of them together.
Key takeaways- WITH name AS (...)
- Then use it like a table
- Reusable and readable