SQL Chained CTEs

A CTE that builds on another CTE — each step reads cleanly, with no nesting.

What & Why

A later CTE can reference an earlier one in the same WITH block. This is how you break a multi-step calculation into named, readable stages instead of one long nested subquery.

See How It Works

BUSINESS QUESTION

Marketing wants converted leads staged, grouped, and ranked by source.

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

Watch converted lead sources become a ranked reportStep 1 of 3

converted → source_totals → ranked

source
google_ads
linkedin

converted exposes only the source column selected by the first CTE.

EXAMPLE QUERY

WITH converted AS (
  SELECT source
  FROM marketing.leads
  WHERE converted_at IS NOT NULL
),
source_totals AS (
  SELECT source, COUNT(*) AS conversions
  FROM converted
  GROUP BY source
),
ranked AS (
  SELECT source, conversions, RANK() OVER (ORDER BY conversions DESC) AS source_rank
  FROM source_totals
)
SELECT source, conversions, source_rank
FROM ranked
ORDER BY source_rank, source;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice