Churn Candidates

Problem

Marketing wants a daily list of prepaid subscribers who look like they are about to churn: they topped up at least once in the 90 days before the cutoff date, but not at all in the last 60 days. For each, return msisdn, their last top-up date, and how much they spent on top-ups in the last 180 days.

subscribers(msisdn PK, plan, activated_at)         -- plan: prepaid | postpaid
topups(msisdn FK, amount, topup_at)

The cutoff is a parameter (:cutoff) so the same query runs every morning.

Examples

Example 1, cutoff = 2026-09-01

0100 prepaid  topups: 2026-03-01 (30), 2026-06-15 (50)
0101 prepaid  topups: 2026-08-20 (20)
0102 postpaid topups: 2026-06-10 (100)
0103 prepaid  topups: 2026-05-01 (40)

→ only 0100: last top-up 2026-06-15, spend_180d 50 (the March top-up is older than 180 days). 0101 is active, 0102 is postpaid, 0103 is silent for more than 90 days so it is already churned, not "about to".

Example 2, a prepaid subscriber with no top-ups at all → not returned (no last top-up to report).

Constraints

• topups has ~2B rows partitioned by month; subscribers ~40M. Prune partitions: the query must not scan more than 180 days.
• Return at most one row per msisdn.
• Oracle or Postgres syntax is fine; say which.

What they look for

Aggregate per subscriber with MAX(topup_at) and a conditional SUM, then a HAVING on the max date window. Filtering topup_at >= cutoff - 180 days in WHERE for partition pruning before aggregating. Understanding that HAVING MAX(topup_at) < cutoff - 60 is the "silent for 60 days" condition and >= cutoff - 90 keeps it to recent churners. Common bug: computing spend without the 180-day condition, or using WHERE topup_at < cutoff - 60, which drops the recent top-ups you need to exclude the subscriber.