SQL Medium Asked at SnowflakeAsked at DatabricksAsked at Amazon

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

For Data Analyst Data Scientist Data Engineer

The short answer

Multiple CTEs are defined in a single WITH clause separated by commas; each CTE can reference any CTE defined before it in the list but not one defined after. The entire WITH block is logically evaluated before the final SELECT, and each CTE is visible throughout the rest of the query including inside other CTEs.

How to think about it

What the question is really testing

CTE chaining is about readability architecture. Anyone can write a single tangled subquery. The question tests whether you can structure complex logic into named, self-documenting steps — and whether you know the scoping rules that govern what each step can see.

The mental model: a pipeline of named steps

Think of each CTE as a named temporary result set. You define them in order, each one can see everything defined before it, and the final SELECT ties them all together.

WITH
  step_1 AS (...),        -- raw filter
  step_2 AS (... FROM step_1 ...),  -- aggregation using step_1
  step_3 AS (... FROM step_2 ...)   -- classification using step_2
SELECT ... FROM step_3 JOIN step_2 ...;

Forward references — a CTE trying to reference one defined after it — are not allowed. The order in the WITH block is the dependency order.

A worked example: session analysis pipeline

Three-step chain: filter active sessions → aggregate per user → classify engagement tier.

WITH
active_sessions AS (
    SELECT
        user_id,
        session_id,
        (julianday(ended_at) - julianday(started_at)) * 1440 AS duration_min
    FROM sessions
    WHERE ended_at IS NOT NULL
),

user_stats AS (
    SELECT
        user_id,
        COUNT(*)          AS session_count,
        AVG(duration_min) AS avg_duration,
        SUM(duration_min) AS total_duration
    FROM active_sessions
    GROUP BY user_id
),

engagement_tiers AS (
    SELECT
        user_id,
        session_count,
        avg_duration,
        CASE
            WHEN total_duration >= 120 THEN 'high'
            WHEN total_duration >= 30  THEN 'medium'
            ELSE 'low'
        END AS engagement
    FROM user_stats
)

SELECT et.user_id, et.engagement, us.session_count, ROUND(us.avg_duration, 1) AS avg_min
FROM engagement_tiers et
JOIN user_stats us ON et.user_id = us.user_id
WHERE et.engagement = 'high'
ORDER BY us.total_duration DESC;

Try it live

The playground below has sample session data. Experiment: change the WHEN total_duration >= 120 threshold, or add a fourth CTE that ranks users within each engagement tier.

The scoping rules in plain English

Each CTE can reference any CTE above it in the WITH list — not below.
All CTEs in the block are visible to the final SELECT (you can join step_1 and step_3 directly in the final query, as in the example above).
In most engines (PostgreSQL, SQLite, DuckDB), CTEs without side effects are inlined by the optimizer — they behave like subqueries, not materialized temp tables. In PostgreSQL you can force materialization with AS MATERIALIZED (...).

Naming advice

Name each CTE after what it represents, not how it was computed. active_sessions, user_stats, engagement_tiers communicate intent at a glance. cte1, tmp, step2 do not.

Learn it properly CTEs (WITH)