Campaigns Outspending Their Channel's Average — SQL Problem
The media director wants to know which campaigns are taking more than their fair share of a channel's money.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #287
Problem statement
The media director wants to know which campaigns are taking more than their fair share of a channel's money. A campaign's spend is the sum of its daily spend rows; campaigns with no spend rows have not launched and take no part in the comparison.
Return campaign_id, campaign_name, channel and total_spend for every launched campaign whose total spend is strictly greater than the average total spend of the launched campaigns in its own channel. Order by channel, then total_spend descending, then campaign_id.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| channel | enum(search, social, display, video, email) |
Primary key: campaign_id.
One row per campaign.
Table: DailySpend
| Column | Type |
|---|---|
| campaign_id | int |
| spend_date | date |
| spend | int |
Primary key: campaign_id, spend_date.
One row per campaign per day it spent, in rupees.
Examples
Example 1
Campaign
| campaign_id | campaign_name | channel |
|---|---|---|
| 1 | Diwali Mega Sale | search |
| 2 | Exam Season Notes | search |
| 3 | Back to College | search |
| 4 | Holi Colours Kit | social |
| 5 | Eid Festive Edit | social |
| 6 | Winter Skincare | social |
| 7 | Onam Festive Deals | video |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-10-25 | 60000 |
| 1 | 2024-10-26 | 40000 |
| 2 | 2024-10-25 | 30000 |
| 3 | 2024-10-25 | 20000 |
| 4 | 2024-03-20 | 25000 |
| 5 | 2024-04-10 | 25000 |
| 7 | 2024-09-01 | 15000 |
Output
| campaign_id | campaign_name | channel | total_spend |
|---|---|---|---|
| 1 | Diwali Mega Sale | search | 100000 |
How to solve Campaigns Outspending Their Channel's Average
Two levels of aggregation are involved. First, each campaign's total spend: join Campaign to DailySpend and sum per campaign; the inner join leaves out campaigns that never spent, as the statement asks. Second, the average of those totals per channel. Averaging the daily rows directly would be a different number — it weights a campaign by how many days it ran.
With the totals in a CTE, a correlated subquery computes the channel's average for each row and the outer filter keeps totals strictly above it; a channel with one launched campaign never qualifies, because a total is never above itself. A window function can sit on top of the aggregate (AVG(SUM(spend)) OVER (PARTITION BY channel)), which avoids reading the totals twice, and the third form joins a per-channel averages table.
Equal totals are ordered by id, so the order is complete. Cost: one aggregate pass plus one per-channel average.
Reference solution (MySQL)
WITH totals AS (
SELECT c.campaign_id, c.campaign_name, c.channel, SUM(s.spend) AS total_spend
FROM Campaign c
JOIN DailySpend s ON s.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name, c.channel
)
SELECT campaign_id, campaign_name, channel, total_spend
FROM totals t
WHERE total_spend > (SELECT AVG(u.total_spend) FROM totals u WHERE u.channel = t.channel)
ORDER BY channel, total_spend DESC, campaign_idAnother way
SELECT campaign_id, campaign_name, channel, total_spend FROM (
SELECT c.campaign_id, c.campaign_name, c.channel, SUM(s.spend) AS total_spend,
AVG(SUM(s.spend)) OVER (PARTITION BY c.channel) AS channel_avg
FROM Campaign c JOIN DailySpend s ON s.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name, c.channel
) x WHERE total_spend > channel_avg
ORDER BY channel, total_spend DESC, campaign_idAnother way
WITH totals AS (SELECT campaign_id, SUM(spend) AS total_spend FROM DailySpend GROUP BY campaign_id),
avgs AS (SELECT c.channel, AVG(t.total_spend) AS a FROM totals t JOIN Campaign c ON c.campaign_id = t.campaign_id GROUP BY c.channel)
SELECT c.campaign_id, c.campaign_name, c.channel, t.total_spend
FROM totals t JOIN Campaign c ON c.campaign_id = t.campaign_id JOIN avgs g ON g.channel = c.channel
WHERE t.total_spend > g.a
ORDER BY c.channel, t.total_spend DESC, c.campaign_id← Email Open Rate by Hour of Send · Weekly Leads by UTM Medium →