Real-world recipes · SaaS finance
Monthly churn rate
Churn rate for a month = subscriptions cancelled during the month ÷ subscriptions active at the start of it.
We build the month list again, then count both numbers per month with the CASE trick from the COUNTIF lesson. * 100.0 makes it a percentage and forces decimal division.
Try itEdit the query, then Run
| month | active_at_start | cancelled | churn_pct |
|---|---|---|---|
| 2026-02 | 21 | 1 | 4.8 |
| 2026-03 | 61 | 6 | 9.8 |
| 2026-04 | 123 | 18 | 14.6 |
| 2026-05 | 149 | 13 | 8.7 |
| 2026-06 | 196 | 12 | 6.1 |
5 rows
This counts plan rows, so an upgrade (one row ends, a new one starts) shows up as a cancel. For strict customer churn, count distinct customer_id instead, and treat a customer as churned only when they have no active row left.
Your turn
How many subscriptions were cancelled in each month? Columns month and cancellations, in order, skipping active ones.
WHERE cancelled_at IS NOT NULL, GROUP BY strftime('%Y-%m', cancelled_at).
This page on one card
