Campaigns Outspending Their Channel's Average

Medium
287.

Campaigns Outspending Their Channel's Average

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

ColumnTypeValues
campaign_idint
campaign_namevarchar
channelenumsearch, social, display, video, email

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key (together): campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_namechannel
1Diwali Mega Salesearch
2Exam Season Notessearch
3Back to Collegesearch
4Holi Colours Kitsocial
5Eid Festive Editsocial
6Winter Skincaresocial
7Onam Festive Dealsvideo

DailySpend

campaign_idspend_datespend
12024-10-2560000
12024-10-2640000
22024-10-2530000
32024-10-2520000
42024-03-2025000
52024-04-1025000
72024-09-0115000

Output

campaign_idcampaign_namechanneltotal_spend
1Diwali Mega Salesearch100000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
campaign_idint
campaign_namevarchar
channelenum(search, social, display, video, email)

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key: campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Examples

Example 1

Campaign

campaign_idcampaign_namechannel
1Diwali Mega Salesearch
2Exam Season Notessearch
3Back to Collegesearch
4Holi Colours Kitsocial
5Eid Festive Editsocial
6Winter Skincaresocial
7Onam Festive Dealsvideo

DailySpend

campaign_idspend_datespend
12024-10-2560000
12024-10-2640000
22024-10-2530000
32024-10-2520000
42024-03-2025000
52024-04-1025000
72024-09-0115000

Output

campaign_idcampaign_namechanneltotal_spend
1Diwali Mega Salesearch100000

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_id

Another 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_id

Another 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 →