SQL Nested Subqueries

A subquery inside a subquery — each layer resolves before the one wrapping it.

What & Why

Subqueries can nest more than one level deep. Postgres resolves from the innermost query outward — each layer becomes a plain value (or list, or table) for the layer around it.

See How It Works

BUSINESS QUESTION

Find campaigns above the average spend of channels that have produced a lead.

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

EXAMPLE QUERY

SELECT
  name,
  channel,
  spend
FROM marketing.campaigns
WHERE spend > (
  SELECT AVG(spend)
  FROM marketing.campaigns
  WHERE id IN (
    SELECT DISTINCT campaign_id
    FROM marketing.leads
    WHERE campaign_id IS NOT NULL
  )
)
ORDER BY spend DESC, name;

RESULT — above the 50,000 qualifying-campaign average

name	channel	spend
Enterprise Search	google_ads	60000.00
Spring Launch	google_ads	55000.00

The inner ID set is 1, 2, and 3, whose average spend is 50,000.

Advanced business practice