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
topupshas~2Brows 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.