Campaign Months That Overspent the Budget — SQL Problem
Each campaign has a fixed monthly budget, and the agency must explain to the client every calendar month in which a campaign spent more than it.
- Difficulty: Medium
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #284
Problem statement
Each campaign has a fixed monthly budget, and the agency must explain to the client every calendar month in which a campaign spent more than it.
For each campaign and calendar month with spend, add up the daily spend. Return campaign_id, month (as 'YYYY-MM'), month_spend and overspend (month_spend − monthly_budget) for the months where month_spend is strictly greater than the budget. A campaign with a NULL budget is never reported. Order by campaign_id, then month.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| monthly_budget | int |
Primary key: campaign_id.
One row per campaign; monthly_budget in rupees, NULL when not yet approved.
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 | monthly_budget |
|---|---|---|
| 1 | Navratri Ethnic Wear | 50000 |
| 2 | Winter Skincare | 30000 |
| 3 | Pongal Kitchen Week | NULL |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-09-29 | 30000 |
| 1 | 2024-09-30 | 22000 |
| 1 | 2024-10-01 | 40000 |
| 1 | 2024-10-02 | 10000 |
| 2 | 2024-11-10 | 18000 |
| 2 | 2024-11-11 | 12000 |
| 2 | 2024-12-01 | 31000 |
| 3 | 2024-12-28 | 90000 |
Output
| campaign_id | month | month_spend | overspend |
|---|---|---|---|
| 1 | 2024-09 | 52000 | 2000 |
| 2 | 2024-12 | 31000 | 1000 |
How to solve Campaign Months That Overspent the Budget
The comparison is between a monthly total and the budget, so first collapse the daily rows into (campaign, month) buckets. DATE_FORMAT(spend_date, '%Y-%m') is the bucket and also the output format; LEFT(spend_date, 7) gives the same text because the dates are stored as 'YYYY-MM-DD'. A campaign running across 30 September and 1 October lands in two buckets, each judged on its own.
Then join the budget and keep the buckets strictly above it. A NULL budget makes month_spend > NULL unknown, so those campaigns drop out without a special case. The alternatives group and compare in one query with HAVING — the budget must then be grouped on or wrapped in MAX so no bare column sits under GROUP BY.
Cost: one aggregate over the spend rows and one key lookup per bucket.
Reference solution (MySQL)
SELECT c.campaign_id, m.month, m.month_spend, m.month_spend - c.monthly_budget AS overspend
FROM (
SELECT campaign_id, DATE_FORMAT(spend_date, '%Y-%m') AS month, SUM(spend) AS month_spend
FROM DailySpend
GROUP BY campaign_id, DATE_FORMAT(spend_date, '%Y-%m')
) m
JOIN Campaign c ON c.campaign_id = m.campaign_id
WHERE m.month_spend > c.monthly_budget
ORDER BY c.campaign_id, m.monthAnother way
SELECT s.campaign_id, LEFT(s.spend_date, 7) AS month, SUM(s.spend) AS month_spend, SUM(s.spend) - c.monthly_budget AS overspend
FROM DailySpend s JOIN Campaign c ON c.campaign_id = s.campaign_id
WHERE c.monthly_budget IS NOT NULL
GROUP BY s.campaign_id, LEFT(s.spend_date, 7), c.monthly_budget
HAVING SUM(s.spend) > c.monthly_budget
ORDER BY s.campaign_id, monthAnother way
SELECT s.campaign_id, CONCAT(YEAR(s.spend_date), '-', LPAD(MONTH(s.spend_date), 2, '0')) AS month, SUM(s.spend) AS month_spend,
SUM(s.spend) - MAX(c.monthly_budget) AS overspend
FROM DailySpend s JOIN Campaign c ON c.campaign_id = s.campaign_id
GROUP BY s.campaign_id, CONCAT(YEAR(s.spend_date), '-', LPAD(MONTH(s.spend_date), 2, '0'))
HAVING SUM(s.spend) > MAX(c.monthly_budget)
ORDER BY s.campaign_id, month← Top Two Ads by Conversions in Each Campaign · Last-Touch Channel for Each Conversion →