SQL CTE with JOIN

A CTE joins to a real table exactly like any other table would.

What & Why

Once a CTE resolves, it behaves like a table for the rest of the query — including being the left or right side of a JOIN.

See How It Works

BUSINESS QUESTION

Marketing joins one lead summary per campaign back to campaign details.

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

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

WITH lead_summary AS (
  SELECT
    campaign_id,
    COUNT(*) AS leads,
    COUNT(*) FILTER (WHERE converted_at IS NOT NULL) AS conversions
  FROM marketing.leads
  WHERE campaign_id IS NOT NULL
  GROUP BY campaign_id
)
SELECT
  c.id AS campaign_id,
  c.name,
  COALESCE(ls.leads, 0) AS leads,
  COALESCE(ls.conversions, 0) AS conversions
FROM marketing.campaigns c
LEFT JOIN lead_summary ls ON ls.campaign_id = c.id
ORDER BY campaign_id;

RESULT — one row per campaign

campaign_id	name	leads	conversions
1	Spring Launch	2	1
2	Retention Webinar	1	0
3	Finance Retargeting	1	1
4	Enterprise Search	0	0

Pre-aggregation prevents the child join from multiplying campaign rows.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice