Subscription Churn by Plan
In subscriptions, an active subscription has a NULL ended_at; a churned one has an end date.
For each plan, report:
- total_subs: number of subscriptions
- churned_subs: number with a non-NULL ended_at
- churn_rate_pct: 100.0 * churned_subs / total_subs, ROUND(x, 2)
Return columns plan, total_subs, churned_subs, churn_rate_pct. Order by churn_rate_pct descending, then plan ascending.
Schema
CREATE TABLE subscriptions ( sub_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, plan TEXT NOT NULL, started_at TEXT NOT NULL, ended_at TEXT );
Sample data
subscriptions
| sub_id | user_id | plan | started_at | ended_at |
|---|---|---|---|---|
| 1 | 1 | basic | 2025-09-01 | 2025-12-01 |
| 2 | 2 | basic | 2025-09-10 | NULL |
| 3 | 3 | pro | 2025-10-01 | NULL |
| 4 | 4 | pro | 2025-10-05 | NULL |
| 5 | 5 | basic | 2025-11-01 | 2026-01-15 |
| 6 | 6 | team | 2025-11-20 | 2026-02-01 |
| 7 | 7 | pro | 2025-12-01 | 2026-03-01 |
| 8 | 8 | team | 2026-01-01 | NULL |
Hint 1
SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts the rows that meet a condition.
Hint 2
COUNT(ended_at) also works, because COUNT(column) skips NULLs.
Good practice for: Data Analyst, Product Manager, Business Analyst, Data Scientist
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.