How do you chain multiple CTEs in a single query, and what are the scoping and execution rules you need to know?

How to think about it

CTE chaining is really about readability architecture. Anyone can write one tangled subquery; this question asks whether you can break complex logic into named, self-documenting steps — and whether you know the scoping rules that govern what each step can see.

The mental model is a pipeline of named result sets. You define them in order, each one can see everything defined above it, and the final SELECT ties them together. Forward references — a CTE pointing at one defined after it — are not allowed; the order in the WITH block is the dependency order.

A worked example — a session-analysis pipeline

Three steps: filter completed sessions → aggregate per user → classify an engagement tier. Each CTE reads from the one before it:

WITH
active_sessions AS (                                   -- step 1: per-session durations
  SELECT user_id,
         (julianday(ended_at) - julianday(started_at)) * 1440 AS duration_min
  FROM sessions
  WHERE ended_at IS NOT NULL
),
user_stats AS (                                        -- step 2: roll up to the user
  SELECT user_id,
         COUNT(*)          AS session_count,
         SUM(duration_min) AS total_duration
  FROM active_sessions
  GROUP BY user_id
),
engagement_tiers AS (                                  -- step 3: classify
  SELECT user_id, session_count,
         ROUND(total_duration, 1) AS total_min,
         CASE WHEN total_duration >= 120 THEN 'high'
              WHEN total_duration >= 30  THEN 'medium'
              ELSE 'low' END AS engagement
  FROM user_stats
)
SELECT * FROM engagement_tiers ORDER BY total_min DESC;

user_id	session_count	total_min	engagement
3	2	270.0	high
1	3	155.0	high
2	2	25.0	low

You can read the pipeline straight down: user 4’s open session (a NULL ended_at) was dropped at step 1, so they never reach the output; step 2 summed each remaining user’s minutes; step 3 labelled them. User 3 (270 min) and user 1 (155 min) clear the 120-minute “high” bar; user 2’s 25 minutes falls below even “medium.” Each named step says what it represents, so the logic is auditable at a glance.

The scoping rules in plain English

• Each CTE may reference any CTE above it in the WITH list — never below.
• All CTEs are visible to the final SELECT, so you can join an early step and a late step directly.
• In most engines (PostgreSQL, SQLite, DuckDB) a side-effect-free CTE is inlined by the optimiser — it behaves like a subquery, not a materialised temp table. PostgreSQL lets you force the other behaviour with AS MATERIALIZED (...).

Name each CTE after what it represents — active_sessions, user_stats, engagement_tiers — not cte1, tmp, step2. The names are the documentation.