SQL Correlated Subquery
A subquery that re-runs once per outer row — because it references that row.
What & Why
A correlated subquery references a column from the outer query inside its own WHERE clause. That reference means it can't run once and be done — it re-evaluates separately for every single row the outer query considers.
See How It Works
BUSINESS QUESTION
Find campaigns whose spend is above the average for their own channel.
| 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 |
Watch the peer average follow each campaign's channelStep 1 of 4
peer.channel = current_campaign.channel
| name | channel | spend | channel_avg | decision |
|---|---|---|---|---|
| Spring Launch | google_ads | 55,000.00 | 57,500.00 | SKIP |
| Retention Webinar | 45,000.00 | 45,000.00 | SKIP | |
| Finance Retargeting | 50,000.00 | 50,000.00 | SKIP | |
| Enterprise Search | google_ads | 60,000.00 | 57,500.00 | KEEP |
Spring Launch is below the average of its two google_ads peers.
EXAMPLE QUERY
SELECT
c.name,
c.channel,
c.spend
FROM marketing.campaigns c
WHERE c.spend > (
SELECT AVG(peer.spend)
FROM marketing.campaigns peer
WHERE peer.channel = c.channel
)
ORDER BY c.channel, c.spend DESC;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario