SQL CTE with Aggregation

Pre-compute the grouped numbers inside the CTE — the outer query just reads finished results.

What & Why

Aggregating inside a CTE keeps the calculation and the filtering on top of it visually separate — you compute the numbers in one named step, then filter or sort them in the next.

See How It Works

BUSINESS QUESTION

Count total and converted leads for each campaign ID.

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

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
  campaign_id,
  leads,
  conversions
FROM lead_summary
ORDER BY leads DESC, campaign_id;

RESULT — lead summary by campaign ID

campaign_id	leads	conversions
1	2	1
2	1	0
3	1	1

The CTE returns only campaign IDs present in marketing.leads, ordered by lead count and then campaign ID.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice