SQL Multiple CTEs
One WITH, separated by commas — each CTE resolves independently of the others.
What & Why
You can define more than one CTE in a single WITH — separate them with commas. Each one is independent of the others (unless you deliberately chain them, covered next) and can be referenced separately in the final query.
See How It Works
BUSINESS QUESTION
Prepare active campaigns and lead totals independently, then join the two named tables.
| 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 | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 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 |
| id | campaign_id | 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 | 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 | US |
Watch two independent CTE tables join into one resultStep 1 of 3
active_campaigns AS (SELECT ... FROM marketing.campaigns WHERE status = 'active')
| campaign_id | name | channel |
|---|---|---|
| 1 | Spring Launch | google_ads |
| 2 | Retention Webinar | |
| 3 | Finance Retargeting | |
| 4 | Enterprise Search | google_ads |
The first independent CTE returns the four active campaign rows.
EXAMPLE QUERY
WITH active_campaigns AS (
SELECT id AS campaign_id, name, channel
FROM marketing.campaigns
WHERE status = 'active'
),
lead_totals AS (
SELECT campaign_id, COUNT(*) AS leads
FROM marketing.leads
WHERE campaign_id IS NOT NULL
GROUP BY campaign_id
)
SELECT
a.campaign_id,
a.name,
a.channel,
COALESCE(l.leads, 0) AS leads
FROM active_campaigns a
LEFT JOIN lead_totals l ON l.campaign_id = a.campaign_id
ORDER BY a.campaign_id;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario