Allowed R6 · Within policy
{
"slug": "subscriber-churn-query",
"title": "Subscriber Churn Query",
"difficulty": "medium",
"tags": [
"sql",
"data",
"technical",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.75,
"last_reported_at": "2026-07-17T16:54:46.104500Z",
"url": "https://www.mokabla.com/en/c/orange/subscriber-churn-query",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/orange/subscriber-churn-query",
"question": "https://www.mokabla.com/agent/_call/get_question?company=orange&question=subscriber-churn-query"
},
"company": {
"slug": "orange",
"name_en": "Orange Egypt / Orange Business"
},
"body_md": "## Problem\nTwo tables from the billing warehouse:\n\n- `subscribers(msisdn, plan, activated_on)`\n- `monthly_usage(msisdn, month, data_mb, voice_min)`, one row per subscriber per month **only if** they used anything\n\nDefine a subscriber as **churned in month M** if they had a usage row in month M-1 but none in M. Write a query that returns, for each month of 2026 up to June, the number of churned subscribers and the churn rate as a percentage of the previous month's active subscribers, rounded to two decimals.\n\n## Examples\n**Example 1**\nJanuary has 4 active subscribers; in February three of them have usage rows and one does not. February row: `2026-02, 1, 25.00`.\n\n**Example 2**\nA subscriber active in January, absent in February, back in March. They count as churned in February and as active again in March, but they must **not** count as churned in March.\n\n## Constraints\n- `month` is stored as `'YYYY-MM'` text\n- Standard SQL (window functions and CTEs allowed, SQLite-compatible preferred)\n- Months where nobody churned should still appear with 0 and 0.00\n\n## What they look for\nA self-join or `LEFT JOIN` between month M-1 and M on `msisdn` with a NULL check, a months spine so empty months appear, and correct rate denominators. The month-arithmetic on a text column is the practical annoyance they want you to handle cleanly.",
"follow_ups_md": "- Extend the definition to 'no usage for two consecutive months'.\n- How would you attribute churn to a plan?\n- The table has 30M rows per month; what does the plan look like and what index helps?",
"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."
}