Meta: Advertisers Whose Spend Fell Three Months Running
The ads sales team wants an early warning for advertisers who are pulling back. Take each advertiser's total spend per calendar month.
- Difficulty: Medium
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #760
Problem statement
The ads sales team wants an early warning for advertisers who are pulling back. Take each advertiser's total spend per calendar month. A decline month is a month m such that the advertiser spent in m, in the month before it and in the month before that (three consecutive calendar months), and each of the two later months is strictly lower than the month before it.
Return one row per decline month, with the columns advertiser_id and decline_month (formatted YYYY-MM). A month with no spend breaks the chain, equal totals are not a decline, and a run can cross a year boundary. 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: AdvertiserSpend
| Column | Type |
|---|---|
| spend_id | int |
| advertiser_id | int |
| spend_date | date |
| amount | int |
Primary key: spend_id.
One row per billed charge, in thousands of rupees; an advertiser can have several in a month.
Examples
Example 1
AdvertiserSpend
| spend_id | advertiser_id | spend_date | amount |
|---|---|---|---|
| 1 | 1 | 2025-01-05 | 200 |
| 2 | 1 | 2025-01-20 | 100 |
| 3 | 1 | 2025-02-10 | 250 |
| 4 | 1 | 2025-03-15 | 200 |
| 5 | 1 | 2025-04-02 | 150 |
| 6 | 2 | 2025-01-08 | 500 |
| 7 | 2 | 2025-02-08 | 400 |
| 8 | 2 | 2025-04-08 | 300 |
| 9 | 3 | 2025-05-09 | 100 |
| 10 | 3 | 2025-06-09 | 90 |
| 11 | 3 | 2025-07-09 | 90 |
| 12 | 4 | 2024-11-30 | 600 |
| 13 | 4 | 2024-12-01 | 500 |
| 14 | 4 | 2025-01-31 | 400 |
Output
| advertiser_id | decline_month |
|---|---|
| 1 | 2025-03 |
| 1 | 2025-04 |
| 4 | 2025-01 |
How to solve Meta: Advertisers Whose Spend Fell Three Months Running
Two steps: aggregate charges into monthly totals, then compare each month with the two before it. The comparison needs calendar neighbours, not merely the previous rows, because a month with no spend must break the chain.
Encoding a month as YEAR * 12 + MONTH makes neighbours arithmetic: the month before mi is mi − 1, and December 2024 sits right before January 2025. Joining the monthly totals to themselves on mi − 1 and mi − 2 finds only genuinely consecutive triples — a gap leaves no partner row — and the WHERE demands two strict drops, so equal totals do not count. The month label is rebuilt from the number with DIV, MOD and LPAD.
The window alternative reads the two previous totals with LAG, then proves consecutiveness by checking that the row two back is exactly two calendar months earlier (DATE_SUB(…, INTERVAL 2 MONTH) on the first of the month). Both group the charges once; the self-join probes the monthly table by key, the window sorts it per advertiser.
Reference solution (MySQL)
WITH monthly AS (
SELECT advertiser_id, YEAR(spend_date) * 12 + MONTH(spend_date) AS mi, SUM(amount) AS total
FROM AdvertiserSpend
GROUP BY advertiser_id, YEAR(spend_date) * 12 + MONTH(spend_date)
)
SELECT m3.advertiser_id,
CONCAT((m3.mi - 1) DIV 12, '-', LPAD((m3.mi - 1) MOD 12 + 1, 2, '0')) AS decline_month
FROM monthly m3
JOIN monthly m2 ON m2.advertiser_id = m3.advertiser_id AND m2.mi = m3.mi - 1
JOIN monthly m1 ON m1.advertiser_id = m3.advertiser_id AND m1.mi = m3.mi - 2
WHERE m2.total < m1.total
AND m3.total < m2.totalAnother way
WITH monthly AS (
SELECT advertiser_id, DATE_FORMAT(spend_date, '%Y-%m') AS ym, SUM(amount) AS total
FROM AdvertiserSpend GROUP BY advertiser_id, DATE_FORMAT(spend_date, '%Y-%m')
), lagged AS (
SELECT advertiser_id, ym, total,
LAG(total, 1) OVER (PARTITION BY advertiser_id ORDER BY ym) AS prev1,
LAG(total, 2) OVER (PARTITION BY advertiser_id ORDER BY ym) AS prev2,
LAG(ym, 2) OVER (PARTITION BY advertiser_id ORDER BY ym) AS ym2
FROM monthly
)
SELECT advertiser_id, ym AS decline_month
FROM lagged
WHERE prev1 < prev2 AND total < prev1
AND ym2 = DATE_FORMAT(DATE_SUB(CONCAT(ym, '-01'), INTERVAL 2 MONTH), '%Y-%m')← Meta: WhatsApp Business Message Delivery Funnel · Meta: Moderators Overturned More Often Than Their Team →