Meta: The Day Each Campaign Exhausted Its Lifetime Budget

Hard
770.

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

ColumnType
campaign_idint
campaign_namevarchar
lifetime_budgetint
end_datedate

Primary key: campaign_id.

One row per campaign; lifetime_budget is in rupees.

Table: CampaignSpend

ColumnType
spend_idint
campaign_idint
spend_datedate
amountint

Primary key: spend_id.

One row per delivery charge; a campaign can have several on one day, and never after its end_date.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Campaign

campaign_idcampaign_namelifetime_budgetend_date
1Diwali Sale100002025-10-25
2Monsoon Offers80002025-10-20
3App Installs50002025-10-12
4Brand Lift30002025-10-30

CampaignSpend

spend_idcampaign_idspend_dateamount
112025-10-104000
212025-10-113000
312025-10-111000
412025-10-132500
522025-10-123000
622025-10-152000
732025-10-112000
832025-10-123000

Output

campaign_idexhausted_ondays_leftoverspend
12025-10-1312500
2NULLNULLNULL
32025-10-1200
4NULLNULLNULL
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.

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

ColumnType
campaign_idint
campaign_namevarchar
lifetime_budgetint
end_datedate

Primary key: campaign_id.

One row per campaign; lifetime_budget is in rupees.

Table: CampaignSpend

ColumnType
spend_idint
campaign_idint
spend_datedate
amountint

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_idcampaign_namelifetime_budgetend_date
1Diwali Sale100002025-10-25
2Monsoon Offers80002025-10-20
3App Installs50002025-10-12
4Brand Lift30002025-10-30

CampaignSpend

spend_idcampaign_idspend_dateamount
112025-10-104000
212025-10-113000
312025-10-111000
412025-10-132500
522025-10-123000
622025-10-152000
732025-10-112000
832025-10-123000

Output

campaign_idexhausted_ondays_leftoverspend
12025-10-1312500
2NULLNULLNULL
32025-10-1200
4NULLNULLNULL

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_id

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