Meta: The Day Each Campaign Exhausted Its Lifetime Budget
Ads delivery engineers study budget pacing: a campaign that spends its whole lifetime budget long before its end date was paced badly.
- Difficulty: Hard
- Topics: Window Functions, Joins, Dates
- Dialect: MySQL
- Problem: #770
Problem statement
Ads delivery engineers study budget pacing: a campaign that spends its whole lifetime budget long before its end date was paced badly. A campaign exhausts its budget on the first date on which its cumulative spend (all charges on that date and before) reaches or exceeds lifetime_budget.
Return one row for every campaign, with campaign_id, exhausted_on (that date), days_left (days from exhausted_on to the campaign's end_date; 0 when it ran out on its last day) and overspend (the campaign's total spend minus its budget). For a campaign that never exhausted its budget, all three are NULL. Return the rows in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
| lifetime_budget | int |
| end_date | date |
Primary key: campaign_id.
One row per campaign; lifetime_budget is in rupees.
Table: CampaignSpend
| Column | Type |
|---|---|
| spend_id | int |
| campaign_id | int |
| spend_date | date |
| amount | int |
Primary key: spend_id.
One row per delivery charge; a campaign can have several on one day, and never after its end_date.
Examples
Example 1
Campaign
| campaign_id | campaign_name | lifetime_budget | end_date |
|---|---|---|---|
| 1 | Diwali Sale | 10000 | 2025-10-25 |
| 2 | Monsoon Offers | 8000 | 2025-10-20 |
| 3 | App Installs | 5000 | 2025-10-12 |
| 4 | Brand Lift | 3000 | 2025-10-30 |
CampaignSpend
| spend_id | campaign_id | spend_date | amount |
|---|---|---|---|
| 1 | 1 | 2025-10-10 | 4000 |
| 2 | 1 | 2025-10-11 | 3000 |
| 3 | 1 | 2025-10-11 | 1000 |
| 4 | 1 | 2025-10-13 | 2500 |
| 5 | 2 | 2025-10-12 | 3000 |
| 6 | 2 | 2025-10-15 | 2000 |
| 7 | 3 | 2025-10-11 | 2000 |
| 8 | 3 | 2025-10-12 | 3000 |
Output
| campaign_id | exhausted_on | days_left | overspend |
|---|---|---|---|
| 1 | 2025-10-13 | 12 | 500 |
| 2 | NULL | NULL | NULL |
| 3 | 2025-10-12 | 0 | 0 |
| 4 | NULL | NULL | NULL |
How to solve Meta: The Day Each Campaign Exhausted Its Lifetime Budget
The key quantity is the cumulative spend at the end of each date, so the query first collapses the charges to one row per campaign per day — otherwise two charges on the same day would produce two running totals for that date, and the first of them could look like the day the budget was not yet reached. A window SUM(day_spend) OVER (PARTITION BY campaign_id ORDER BY spend_date) then gives the spend so far on every spending day, and an unordered SUM(...) OVER (PARTITION BY campaign_id) the campaign's total.
The exhaustion date is the earliest date whose running total is at least the budget — reaching it exactly counts — so the query keeps the qualifying days and takes MIN(spend_date). Because the running total only grows, every later day qualifies too, and the minimum is the crossing. The campaign list is the driver of a LEFT JOIN, so campaigns that never ran out, or never spent, keep a row whose exhausted_on is NULL; DATEDIFF and the subtraction then return NULL as well, exactly as asked.
The alternative computes each charge's running total with a correlated SUM over all charges up to that date — equivalent, since the sum covers the whole day — and the total spend with another subquery. It is quadratic per campaign; the window version sorts each campaign's days once.
Reference solution (MySQL)
WITH daily AS (
SELECT campaign_id, spend_date, SUM(amount) AS day_spend
FROM CampaignSpend
GROUP BY campaign_id, spend_date
), running AS (
SELECT campaign_id, spend_date,
SUM(day_spend) OVER (PARTITION BY campaign_id ORDER BY spend_date) AS spent_so_far,
SUM(day_spend) OVER (PARTITION BY campaign_id) AS total_spent
FROM daily
), hit AS (
SELECT r.campaign_id, MIN(r.spend_date) AS exhausted_on, MAX(r.total_spent) AS total_spent
FROM running r
JOIN Campaign c ON c.campaign_id = r.campaign_id
WHERE r.spent_so_far >= c.lifetime_budget
GROUP BY r.campaign_id
)
SELECT c.campaign_id, h.exhausted_on,
DATEDIFF(c.end_date, h.exhausted_on) AS days_left,
h.total_spent - c.lifetime_budget AS overspend
FROM Campaign c
LEFT JOIN hit h ON h.campaign_id = c.campaign_idAnother way
SELECT c.campaign_id, x.exhausted_on,
DATEDIFF(c.end_date, x.exhausted_on) AS days_left,
CASE WHEN x.exhausted_on IS NULL THEN NULL
ELSE (SELECT SUM(t.amount) FROM CampaignSpend t WHERE t.campaign_id = c.campaign_id) - c.lifetime_budget END AS overspend
FROM Campaign c
LEFT JOIN (
SELECT s.campaign_id, MIN(s.spend_date) AS exhausted_on
FROM CampaignSpend s
JOIN Campaign k ON k.campaign_id = s.campaign_id
WHERE (SELECT SUM(s2.amount) FROM CampaignSpend s2
WHERE s2.campaign_id = s.campaign_id AND s2.spend_date <= s.spend_date) >= k.lifetime_budget
GROUP BY s.campaign_id
) x ON x.campaign_id = c.campaign_id← Meta: Each Reels Creator's Percentile Rank Within Their Country · Meta: Instagram Reels Shopping Funnel, Steps in Order →