Campaign Months That Overspent the Budget

Medium
284.

Campaign Months That Overspent the Budget

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

ColumnType
campaign_idint
campaign_namevarchar
monthly_budgetint

Primary key: campaign_id.

One row per campaign; monthly_budget in rupees, NULL when not yet approved.

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_namemonthly_budget
1Navratri Ethnic Wear50000
2Winter Skincare30000
3Pongal Kitchen WeekNULL

DailySpend

campaign_idspend_datespend
12024-09-2930000
12024-09-3022000
12024-10-0140000
12024-10-0210000
22024-11-1018000
22024-11-1112000
22024-12-0131000
32024-12-2890000

Output

campaign_idmonthmonth_spendoverspend
12024-09520002000
22024-12310001000
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.

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

ColumnType
campaign_idint
campaign_namevarchar
monthly_budgetint

Primary key: campaign_id.

One row per campaign; monthly_budget in rupees, NULL when not yet approved.

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_namemonthly_budget
1Navratri Ethnic Wear50000
2Winter Skincare30000
3Pongal Kitchen WeekNULL

DailySpend

campaign_idspend_datespend
12024-09-2930000
12024-09-3022000
12024-10-0140000
12024-10-0210000
22024-11-1018000
22024-11-1112000
22024-12-0131000
32024-12-2890000

Output

campaign_idmonthmonth_spendoverspend
12024-09520002000
22024-12310001000

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.month

Another 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, month

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