SQL Academy · Lesson

Common Table Expressions (WITH)

Refactor nested queries into readable WITH clauses, chain CTEs, and learn the materialisation rules in PostgreSQL 12+.

Common Table Expressions (WITH) is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a CTE?

A Common Table Expression (CTE) is a named subquery defined with WITH:

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, COUNT(*)
FROM paid_orders
GROUP BY user_id;

Why Use CTEs?

Three big wins:

Readability — break a 200-line query into named steps
Reuse — reference the same intermediate result multiple times
Recursion — only CTEs support recursive queries (next lesson)

Chaining CTEs

Define many CTEs in one WITH and use one in the next:

WITH last_30 AS (
  SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
per_user AS (
  SELECT user_id, SUM(total) AS revenue FROM last_30 GROUP BY user_id
)
SELECT u.email, p.revenue
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY p.revenue DESC LIMIT 20;

Reusing a CTE

If the same intermediate is used twice, a CTE makes intent obvious:

WITH recent_users AS (
  SELECT id FROM users WHERE created_at >= NOW() - INTERVAL '7 days'
)
SELECT 'new orders'  AS metric, COUNT(*) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users)
UNION ALL
SELECT 'new revenue', SUM(total) FROM orders
  WHERE user_id IN (SELECT id FROM recent_users);

CTE Materialisation (PostgreSQL ≤ 11)

Older PG versions always materialised CTE output — a planner barrier. From PG 12 the planner inlines CTEs by default unless you say otherwise:

-- Force the old materialise behaviour (rarely needed):
WITH x AS MATERIALIZED (SELECT ...) ...

-- Force inlining (default):
WITH x AS NOT MATERIALIZED (SELECT ...) ...

Data-Modifying CTEs

CTEs can use INSERT/UPDATE/DELETE — handy for moving rows between tables atomically:

WITH moved AS (
  DELETE FROM orders WHERE status = 'archived' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

CTEs in DML

RETURNING + WITH for "find and act":

WITH cancelled AS (
  UPDATE orders SET status = 'cancelled'
  WHERE created_at < NOW() - INTERVAL '14 days'
    AND status = 'pending'
  RETURNING id
)
INSERT INTO audit_log (event, order_id)
SELECT 'auto-cancel', id FROM cancelled;

Order of Execution

Data-modifying CTEs run independently in the same snapshot. Each statement sees the state BEFORE all modifications start — surprising but predictable.

CTEs vs Subqueries vs Views

Comparison:

Subquery — defined inline; single use
CTE — named; used multiple times within the query; gone after the query ends
View — named; persisted; reusable across queries

Don't Overuse CTEs

Wrap-everything-in-a-CTE makes queries readable but can hide cost. With huge intermediate results, the planner may choose worse plans than for an equivalent JOIN.

Recap

CTEs name intermediate queries.

Improve readability
Enable reuse in one query
Required for recursion
Inlined by default in modern PG

Quick Check

What keyword starts a Common Table Expression?

Frequently asked questions

Is the “Common Table Expressions (WITH)” lesson free?

Yes — the full text of “Common Table Expressions (WITH)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Common Table Expressions (WITH)”?

Refactor nested queries into readable WITH clauses, chain CTEs, and learn the materialisation rules in PostgreSQL 12+. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Common Table Expressions (WITH)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy