CTEs (WITH): making complex queries readable

Structure your queries into named steps with WITH and chain CTEs like a pipeline of transformations.

7 min · 3 questions

A subquery nested three levels deep reads from the inside out: unreadable and impossible to revisit six months later. The CTE (Common Table Expression, the WITH clause) flips the reading order: you name each intermediate step just as you would name a function, and the query reads top to bottom. Load this dataset into sqliteonline.com or sqlite3.

SQL

CREATE TABLE payments (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  paid_at TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO payments (customer, paid_at, amount)
VALUES
  ('alice', '2026-01-10', 120.0),
  ('bruno', '2026-01-22', 80.0),
  ('alice', '2026-02-05', 60.0),
  ('chloe', '2026-02-18', 200.0),
  ('bruno', '2026-03-03', 40.0),
  ('alice', '2026-03-15', 90.0),
  ('chloe', '2026-03-28', 150.0),
  ('bruno', '2026-04-09', 300.0);

Dataset for this lesson.

Basic syntax: WITH name AS (SELECT ...) followed by the main query, which uses name like an ordinary table. Here, we compute monthly revenue by extracting the month from the date with substr (SQLite dates are ISO 8601 text, an engine choice rather than a standard).

SQL

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
)
SELECT * FROM monthly ORDER BY month;

A simple CTE: one named, reusable step.

The real power comes from chaining: a single WITH, several CTEs separated by commas, each able to read those defined before it. You build a pipeline: aggregate, compute a statistic, compare. Here, we look for the months whose revenue exceeds the monthly average (expected: 2026-03 and 2026-04, the average being 260).

SQL

WITH monthly AS (
  SELECT substr(paid_at, 1, 7) AS month,
         SUM(amount) AS revenue
  FROM payments
  GROUP BY month
),
stats AS (
  SELECT AVG(revenue) AS avg_revenue
  FROM monthly
)
SELECT m.month, m.revenue,
       ROUND(s.avg_revenue, 1) AS avg_all
FROM monthly AS m
CROSS JOIN stats AS s
WHERE m.revenue > s.avg_revenue
ORDER BY m.month;

Chained CTEs: stats reads monthly, the final SELECT reads both.

Name your CTEs like business functions (monthly, active_users, late_invoices) and debug them in isolation: temporarily replace the final query with SELECT * FROM monthly to check each stage of the pipeline. This is the big advantage over a nested subquery, which cannot be run on its own.

A CTE is not a performance guarantee. Depending on the engine, it is either merged into the main query or materialized in a temporary area: SQLite lets the optimizer decide, PostgreSQL merges by default since version 12 (MATERIALIZED / NOT MATERIALIZED keywords to force it). A CTE referenced twice can therefore be recomputed twice: check with EXPLAIN QUERY PLAN.

Knowledge check

Make sure you remember the key points of this lesson.

How do you declare several CTEs in the same query?
- A WITH keyword before each CTE
- A single WITH, the CTEs separated by commas
- By nesting them inside one another
Can a CTE read another CTE from the same WITH?
- No, each CTE is isolated
- Yes, but only those defined before it
- Yes, in any order
What is the main benefit of a CTE over a nested subquery?
- It is always faster
- It creates a permanent, reusable table
- It names each step and makes the flow readable and testable