SQL Common Table Expressions (CTEs)

What is a CTE in SQL?

A common table expression (CTE) is a named query you define with a WITH block at the top of a statement, then use like a table in the query that follows. It lasts only for that one statement: nothing is created, stored, or left behind.

with monthly as (
  select date_trunc('month', o.order_date) as m,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders o
  join order_items oi on oi.order_id = o.order_id
  group by m
)
select m, revenue
from monthly
order by m

Here monthly is the CTE. It totals revenue per month, and the query underneath selects from it exactly as if it were a real table.

When to use a CTE instead of a subquery

As queries grow, nesting subqueries inside subqueries gets hard to read. A CTE holds the same logic, but naming each intermediate result turns the query into a sequence of steps you read top to bottom, instead of one expression you have to unpick from the inside out.

Do not add a CTE just to make a short query longer. Reach for WITH when a query has a reusable step, a grouped intermediate result, or a piece of logic you want to test on its own before joining it to more tables.

Name the step, not the syntax

A useful CTE name describes the result it produces. order_revenue is clearer than cte1 because the outer query reads like it is selecting from a real table of customer revenue.