Lesson Intermediate

WITH (CTEs)

A Common Table Expression is a subquery you name at the top of your query and reference like a table below it.

sql

WITH cte_name AS ( SELECT ... )
SELECT ... FROM cte_name;

Every CTE is rewritable as a nested subquery. The reason CTEs exist is readability: you give each step a name, and the final SELECT becomes a sentence you can read top to bottom.

Why Use a CTE Over a Subquery #

Three reasons:

Reason	What it buys you
Readability	Each step gets a name. A 5-step pipeline reads like a recipe, not a matryoshka.
Reuse	Reference the CTE more than once in the same query — a subquery would run twice.
Chaining	Later CTEs can reference earlier ones, so you can build up a pipeline.

If none of those apply, a simple subquery is often fine. Reach for CTEs when the query has more than one logical step or the same intermediate result is used twice.

Single CTE #

sql

WITH paid_orders AS (
  SELECT user_id, total
  FROM orders
  WHERE status = 'paid'
)
SELECT
  user_id,
  COUNT(*) AS orders,
  ROUND(SUM(total), 2) AS revenue
FROM paid_orders
GROUP BY user_id
ORDER BY revenue DESC;

Chained CTEs #

Stack multiple CTEs with commas. Each one can reference earlier CTEs, so the query becomes a pipeline.

sql

WITH paid_orders AS (
  SELECT user_id, total
  FROM orders
  WHERE status = 'paid'
),
per_user AS (
  SELECT user_id, SUM(total) AS user_revenue
  FROM paid_orders
  GROUP BY user_id
),
top_users AS (
  SELECT user_id, user_revenue
  FROM per_user
  WHERE user_revenue > 500
)
SELECT u.email, ROUND(t.user_revenue, 2) AS user_revenue
FROM top_users AS t
INNER JOIN users AS u ON u.id = t.user_id;

Same query written as nested subqueries would be a 40-line parenthesis maze. The CTE version names each step and lets you read the final SELECT first to understand intent.

Referencing a CTE Twice #

This is the killer feature. A subquery in FROM can only be used once per appearance; a CTE can be referenced anywhere in the main query, repeatedly.

sql

-- Users whose revenue is above the average user's revenue
WITH user_paid AS (
  SELECT user_id, SUM(total) AS user_revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT user_id, ROUND(user_revenue, 2) AS user_revenue
FROM user_paid
WHERE user_revenue > (SELECT AVG(user_revenue) FROM user_paid);
--                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                    second reference to the same CTE

Warning

CTE optimization depends on the engine.

DuckDB and modern Postgres (12+) inline CTEs by default, with no performance difference vs a subquery.
Postgres pre-12 and some enterprise engines materialize CTEs into a temp result, which can be a pessimization (the optimizer can't push predicates down).
WITH cte AS MATERIALIZED (...) or NOT MATERIALIZED hints exist in Postgres 12+ to control this explicitly.

If you're moving queries between dialects, check whether CTEs are an optimization fence.

Recursive CTEs (Brief Mention) #

CTEs can refer to themselves with WITH RECURSIVE, the pattern for traversing hierarchies (employee → manager → grand-manager) or generating sequences. It's a separate topic covered in the Reference section; flag it now so you know it exists.

Try It #

A three-step pipeline: paid orders → per-user revenue → join to user emails for the final report.

Loading SQL editor...

Practice #

Using CTEs, return every user whose paid revenue is strictly greater than the average paid revenue across all users. Output columns: user_id, email, user_revenue (rounded to 2). No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Using a CTE for a single, simple step, where a plain subquery is usually clearer.
Chaining 10+ CTEs when the logic could collapse into 2 or 3. CTEs aren't free to read.
Assuming CTE behavior is identical across engines. The materialization rules vary.
Forgetting that a CTE can reference only earlier CTEs in the same WITH list; there's no forward reference.
Trying to UPDATE or DELETE from a CTE. Most dialects make CTEs read-only in the query, and modification is a separate statement.

Knowledge check #

3 questions

0 / 3 answered

A CTE (WITH ... AS) is best described as:
Compared to a subquery doing the same logic, a CTE on a modern engine (Postgres 12+, Snowflake, BigQuery, DuckDB):
Inside a WITH clause with three CTEs (a, b, c), which can c reference?

Next Step #

You've finished the Subqueries & CTEs section. Next up: Window Functions, per-row calculations over a window of other rows, the tool that often replaces correlated subqueries.