Allowed R6 · Within policy
{
"slug": "churn-candidates",
"title": "Churn Candidates",
"difficulty": "medium",
"tags": [
"sql",
"technical",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.5,
"last_reported_at": "2026-06-01T11:55:11.190816Z",
"url": "https://www.mokabla.com/en/c/vodafone/churn-candidates",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/vodafone/churn-candidates",
"question": "https://www.mokabla.com/agent/_call/get_question?company=vodafone&question=churn-candidates"
},
"company": {
"slug": "vodafone",
"name_en": "Vodafone Egypt / _VOIS"
},
"body_md": "## Problem\nMarketing 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.\n\n```\nsubscribers(msisdn PK, plan, activated_at) -- plan: prepaid | postpaid\ntopups(msisdn FK, amount, topup_at)\n```\n\nThe cutoff is a parameter (`:cutoff`) so the same query runs every morning.\n\n## Examples\n**Example 1**, `cutoff = 2026-09-01`\n```\n0100 prepaid topups: 2026-03-01 (30), 2026-06-15 (50)\n0101 prepaid topups: 2026-08-20 (20)\n0102 postpaid topups: 2026-06-10 (100)\n0103 prepaid topups: 2026-05-01 (40)\n```\n→ 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\".\n\n**Example 2**, a prepaid subscriber with **no** top-ups at all → not returned (no last top-up to report).\n\n## Constraints\n- `topups` has `~2B` rows partitioned by month; `subscribers` `~40M`. Prune partitions: the query must not scan more than 180 days.\n- Return at most one row per `msisdn`.\n- Oracle or Postgres syntax is fine; say which.\n\n## What they look for\nAggregate 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.",
"follow_ups_md": "- Add the subscriber's average monthly spend over the last 6 months.\n- The list must exclude anyone contacted by marketing in the last 30 days (another table).\n- Make it incremental: only subscribers who newly qualified since yesterday.",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}