Reference

SQL Common Table Expressions (WITH)

Name intermediate results with WITH so a complex query reads as clear, ordered steps.

A common table expression (CTE) is a named query defined in a WITH clause that you can then select from like a table. CTEs turn a deeply nested query into a readable sequence of named steps.

Basic shape

with order_revenue as (
  select o.customer_id,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  group by o.customer_id
)
select customer_id, revenue
from order_revenue
order by revenue desc, customer_id

Practice

Practice this with the interactive CTE lesson Walk an org chart in the recursive CTE lesson

Why use one

A CTE and a subquery in the FROM clause compute the same thing. The CTE wins when the step deserves a name, when you reference it more than once, or when you want to read the query top to bottom instead of inside out.

Chaining multiple CTEs

List several CTEs separated by commas. Each one can build on the CTEs declared before it, so the query reads as a pipeline.

Each step builds on the last

with order_revenue as (
  select o.customer_id,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  group by o.customer_id
),
ranked as (
  select customer_id,
         revenue,
         rank() over (order by revenue desc) as revenue_rank
  from order_revenue
)
select customer_id, revenue, revenue_rank
from ranked
where revenue_rank <= 10
order by revenue_rank, customer_id

Order matters

A CTE can only reference CTEs defined above it in the same WITH clause. List them in dependency order, first step first.

CTE vs subquery vs view

Tool	Reach for it when
`WITH` / CTE	A step needs a name, is reused, or the query reads better as stages.
Subquery	The intermediate result is small, used once, and inline is clear enough.
View	The named query should be reusable across many different queries.

Recursive CTEs

WITH RECURSIVE lets a CTE reference itself, which is how you walk hierarchies (an org chart, a category tree) or generate sequences. A base query seeds the result; the recursive part repeats until it adds no new rows.

Generate numbers 1 through 5

with recursive counter as (
  select 1 as n
  union all
  select n + 1
  from counter
  where n < 5
)
select n
from counter

Watch out

A CTE is a name scoped to a single statement, not a persistent table. Whether its result is materialized or its definition is inlined is an optimizer decision. DuckDB materializes CTEs by default but can inline them when its heuristics allow; you can also use AS MATERIALIZED or AS NOT MATERIALIZED when you have a specific reason to influence that choice.

Lesson

Joining Customers to Orders in SQL

Join two related tables on a key.

Cheatsheet

SQL Joins Cheatsheet

Join types and common join patterns.

Interview

SQL JOIN Interview Questions

Practice join keys, left joins, duplicate rows, and debugging.

Mistake

COUNT or SUM Too High After a JOIN

One-to-many joins can inflate totals.