Meta: Advertisers Whose Spend Fell Three Months Running

Medium
760.

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

ColumnType
spend_idint
advertiser_idint
spend_datedate
amountint

Primary key: spend_id.

One row per billed charge, in thousands of rupees; an advertiser can have several in a month.

Example

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

AdvertiserSpend

spend_idadvertiser_idspend_dateamount
112025-01-05200
212025-01-20100
312025-02-10250
412025-03-15200
512025-04-02150
622025-01-08500
722025-02-08400
822025-04-08300
932025-05-09100
1032025-06-0990
4 more rows

Output

advertiser_iddecline_month
12025-03
12025-04
42025-01
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: 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

ColumnType
spend_idint
advertiser_idint
spend_datedate
amountint

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_idadvertiser_idspend_dateamount
112025-01-05200
212025-01-20100
312025-02-10250
412025-03-15200
512025-04-02150
622025-01-08500
722025-02-08400
822025-04-08300
932025-05-09100
1032025-06-0990
1132025-07-0990
1242024-11-30600
1342024-12-01500
1442025-01-31400

Output

advertiser_iddecline_month
12025-03
12025-04
42025-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.total

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