advertiser_daily(advertiser_id, activity_date, spend)

A month counts as active for an advertiser when they spent more than 0 in it. For each advertiser and each month of 2026 from their first active month onward, classify the month:

• NEW for their first ever active month
• ACTIVE for active this month and active last month
• CHURNED for not active this month but active last month
• REACTIVATED for active this month after a gap

Months that are neither active nor preceded by an active month produce no row, so a long silence shows one CHURNED and then nothing.

Return advertiser_id, month as 'YYYY-MM' and status, ordered by advertiser then month.

NEW wins over REACTIVATED when both would apply, so the first active month has to be checked before the gap test.

Tables

advertiser_daily

advertiser_id  activity_date  spend
-------------  -------------  -----
10             2026-01-10     50.00
10             2026-02-10     60.00
10             2026-05-10     70.00
11             2025-12-10     10.00
11             2026-01-10     20.00
12             2026-03-10     0.00
12             2026-06-10     5.00

Expected result

advertiser_id  month    status
-------------  -------  -----------
10             2026-01  NEW
10             2026-02  ACTIVE
10             2026-03  CHURNED
10             2026-05  REACTIVATED
10             2026-06  CHURNED
11             2026-01  ACTIVE
11             2026-02  CHURNED
12             2026-06  NEW
... 1 more row(s)