Paid Media Spend per Channel in March 2024 — SQL Problem
Spend is recorded per campaign per day; the channel lives on the campaign. The CMO's monthly deck needs the total spend of each channel for March 2024.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #279
Problem statement
Spend is recorded per campaign per day; the channel lives on the campaign. The CMO's monthly deck needs the total spend of each channel for March 2024.
Return channel and total_spend (the sum of spend on days from 2024-03-01 to 2024-03-31) for every channel that spent in March. Channels with no March spend rows are left out. Order by total_spend descending, then channel ascending.
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; spend is in rupees and campaign_id is always in Campaign.
Examples
Example 1
Campaign
| campaign_id | campaign_name | channel |
|---|---|---|
| 1 | Holi Colours Kit | social |
| 2 | Exam Season Notes | search |
| 3 | Summer Cooler Push | display |
| 4 | Cricket World Cup Merch | social |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-03-20 | 12000 |
| 1 | 2024-03-21 | 15000 |
| 2 | 2024-03-01 | 18000 |
| 2 | 2024-02-29 | 9000 |
| 3 | 2024-04-01 | 7000 |
| 4 | 2024-03-31 | 9000 |
Output
| channel | total_spend |
|---|---|
| social | 36000 |
| search | 18000 |
How to solve Paid Media Spend per Channel in March 2024
Join each spend row to its campaign to learn the channel, keep only March's rows, and sum per channel. Filtering in WHERE before the GROUP BY means a channel whose only rows fall outside March produces no group at all, which is exactly "left out".
The month can be written as a half-open range (>= '2024-03-01' AND < '2024-04-01'), as YEAR(...) = 2024 AND MONTH(...) = 3, or as DATE_FORMAT(spend_date, '%Y-%m') = '2024-03'. The range is the one that can use an index on spend_date; the others are easier to read. Several campaigns can share a channel, so grouping by the campaign would split the channel's total.
Equal totals are ordered by the channel name, so the order is complete. Cost: one pass over the spend rows plus a primary-key lookup per row.
Reference solution (MySQL)
SELECT c.channel, SUM(s.spend) AS total_spend
FROM DailySpend s
JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE s.spend_date >= '2024-03-01' AND s.spend_date < '2024-04-01'
GROUP BY c.channel
ORDER BY total_spend DESC, c.channelAnother way
SELECT c.channel, SUM(s.spend) AS total_spend FROM Campaign c JOIN DailySpend s ON s.campaign_id = c.campaign_id AND YEAR(s.spend_date) = 2024 AND MONTH(s.spend_date) = 3 GROUP BY c.channel ORDER BY total_spend DESC, c.channelAnother way
SELECT channel, SUM(spend) AS total_spend FROM (SELECT (SELECT channel FROM Campaign c WHERE c.campaign_id = s.campaign_id) AS channel, spend FROM DailySpend s WHERE DATE_FORMAT(spend_date, '%Y-%m') = '2024-03') t GROUP BY channel ORDER BY total_spend DESC, channel← Instagram Creator Deals Signed in Q3 2024 · Label Each Campaign by Its Monthly Budget Tier →