SQL Customers with No Orders

One of the most common interview questions — and the wrong tool (NOT IN) has a real, dangerous trap.

What & Why

The customers-with-no-orders pattern keeps a parent row only when no matching child row exists. The same pattern answers Queryflo's internal equivalent: campaigns that have generated no leads.

See How It Works

BUSINESS QUESTION

Find campaigns that have not generated any leads.

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

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 the anti-join find the campaign with no leadsStep 1 of 4

NOT EXISTS marketing.leads for the current campaign

campaign_id	campaign	lead_count	decision
1	Spring Launch	2	SKIP
2	Retention Webinar	1	SKIP
3	Finance Retargeting	1	SKIP
4	Enterprise Search	0	KEEP

Spring Launch has two child rows.

EXAMPLE QUERY

SELECT
  c.id,
  c.name,
  c.channel
FROM marketing.campaigns c
WHERE NOT EXISTS (
  SELECT 1
  FROM marketing.leads l
  WHERE l.campaign_id = c.id
)
ORDER BY c.name;

This lesson's practice is part of Pro.

See what's included →

Advanced business practice