Subscriber Churn Query

Problem

Two tables from the billing warehouse:

• subscribers(msisdn, plan, activated_on)
• monthly_usage(msisdn, month, data_mb, voice_min) — one row per subscriber per month only if they used anything

Define 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.

Examples

Example 1 January has 4 active subscribers; in February three of them have usage rows and one does not. February row: 2026-02, 1, 25.00.

Example 2 A 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.

Constraints

• month is stored as 'YYYY-MM' text
• Standard SQL (window functions and CTEs allowed, SQLite-compatible preferred)
• Months where nobody churned should still appear with 0 and 0.00

What they look for

A 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.