SQL Mastery
Intermediate 15 min read SQL Mastery

CTEs and Subqueries

Name intermediate results; compose instead of nest

Common Table Expressions name intermediate results, making complex logic readable and composable — and recursive CTEs walk trees and hire charts.

WITHCTEsubqueryrecursivereadability
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Structure multi-step SQL with WITH clauses
  • Know when a CTE materializes vs inlines in the planner
  • Use recursive CTEs for hierarchies
Introduction

What is this?

A CTE (`WITH name AS (SELECT ...)`) defines a named result you can reference later in the query, readable top-down instead of nested inside parentheses. Subqueries are anonymous versions of the same idea inside FROM/WHERE. Recursive CTEs extend this to walk trees and graphs row by row.

Motivation

Why do you need it?

  • CTEs convert unreadable 40-line nested queries into a sequence of named, reviewable steps.
  • Recursive CTEs replace application-level tree-walking logic when you must model hierarchies.
  • Good CTE names document intent — the same way function names document code.
Think about it

Mental model

Imagine

Labeling jars while cooking

Think of

CTEs

You wouldn't keep every intermediate in an unlabeled bowl; you label them (‘mirepoix’, ‘roux’) to assemble the dish. CTEs label each intermediate result so the final SELECT reads like a recipe.

Mechanics

How it works

  • WITH raw AS (...), clean AS (...) lets later CTEs reference earlier ones.
  • A subquery in FROM behaves like a derived table you can't reference twice.
  • Planners in modern warehouses often inline CTEs (a 'view'), not materialize; only mutually-recursive or referenced-multiple-times CTEs stage disk.
  • Recursive CTEs have a base term and a recursive term joined by UNION ALL.
  • The recursion terminates with an anchor and a WHERE that bounds the walk.

Layered CTE composition.

CTE raw
CTE clean
CTE metrics
Final SELECT
Hands-on

Examples

A readable multi-step pipeline in SQL

A readable multi-step pipeline in SQL
WITH cleaned AS (
  SELECT id, COALESCE(amount, 0) AS amount
  FROM raw_events
  WHERE id IS NOT NULL
),
daily AS (
  SELECT substr(ts, 1, 10) AS day, SUM(amount) AS revenue
  FROM cleaned
  GROUP BY 1
)
SELECT day, revenue, ROUND(revenue / 1000.0, 2) AS k
FROM daily
ORDER BY day;

Each named step is independently reviewable; the final select is a composition of named results.

Real world

In production

  • dbt models naturally taste like CTE chains — warehouse transforms are written this way and reviewed line by line.
  • A dedup pipeline (ranked CTE → filter rn=1 → join dims) keeps each concern in its own named block.
  • An org-hierarchy report walks the reporting tree with a recursive CTE to depth N.
Guidelines

Best practices

  • Give CTEs intent-revealing names; a query of 8 anonymous subqueries is a maintenance hazard.
  • Prefer CTEs over deep nesting for readability, and check the plan when a 'free' CTE is materializing twice.
  • Use recursion only where the DB is the right tree-walker (small/medium trees); huge graphs belong in Spark/graph engines.
  • Put the final, user-facing projection last — the first lines should be the rawest stage.
Watch out

Common mistakes

  • Stacking subqueries six deep until the query is unreadable and un-diffable.
  • Assuming CTEs are always materialized — the planner may inline them repeatedly, killing performance.
  • Forgetting UNION ALL (not UNION) in a recursive CTE, deduping every level and corrupting tree counts.
  • Infinite recursion from a missing termination predicate.
Root cause analysis

Common issues

Error
Recursive CTE never terminates
Root cause

Missing depth guard or unbounded step

Fix

Add a WHERE on a depth column in the recursive term and confirm base + step decrease.

Error
CTE-like logic duplicated in the plan
Root cause

The planner inlined a multi-referenced CTE instead of materializing

Fix

Force materialization (MATERIALIZED in PostgreSQL) or refactor to avoid repeated computation.

When things break

Debugging tips

  • Run each CTE body alone and inspect its output before assembling the full query.
  • If a CTE is slow, check EXPLAIN for whether it was inlined or materialized.
  • For recursion issues, print the iteration depth column and bound it.
Interviews

Interview checkpoint

Practice

Hands-on lab

Compose a Multi-Step Transform with CTEs
25 min · intermediate
In progress
Objectives
  • Chain named CTE stages readably
  • Use a CTE for dedup + a CTE for aggregation
  • Debug by running each CTE in isolation

Compose a Multi-Step Transform with CTEs

25 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which keyword introduces a named reusable intermediate result in modern SQL?

2

Planners always materialize CTEs to disk exactly once.

Go deeper

Official documentation

PostgreSQL WITH queries
docs
SQLite WITH clause
docs