SQL Filter Early

Cut down the row count as early as possible — every downstream step has less work to do.

What & Why

Filtering early — inside a CTE, before a join, in the innermost subquery — reduces how much data every subsequent step has to process. The planner often reorders things for you, but writing filters early makes the intent explicit and helps in cases the planner can't reorder automatically.

See How It Works

BUSINESS QUESTION

Marketing wants converted leads filtered before they are joined to active campaigns.

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 converted_leads AS (
  SELECT id, campaign_id
  FROM marketing.leads
  WHERE converted_at IS NOT NULL
    AND converted_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00'
)
SELECT
  c.id AS campaign_id,
  c.name,
  COUNT(l.id) AS conversions
FROM converted_leads l
JOIN marketing.campaigns c ON c.id = l.campaign_id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY conversions DESC, campaign_id;

RESULT — active campaigns with conversions

campaign_id	name	conversions
1	Spring Launch	1
3	Finance Retargeting	1

The CTE filters to the two converted leads before joining them to their active campaigns.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice