Code assistant
Churn by plan
Conversation
Give me monthly churn by plan for this year.
10:02
AI
Here is a query that counts cancelled subscriptions against the ones active at the start of each month. It is on the right.
10:02Leave out trial accounts.
10:04Query
Postgres
churn_by_plan.sql
-- monthly churn by plan, paid accounts only
with months as (
select generate_series('2026-01-01'::date, '2026-10-01', '1 month') as month
)
select m.month,
s.plan,
count(*) filter (where s.cancelled_at >= m.month
and s.cancelled_at < m.month + interval '1 month') as cancelled,
count(*) filter (where s.started_at < m.month) as active
from months m
join subscriptions s on s.started_at < m.month + interval '1 month'
where s.trial = false
group by 1, 2
order by 1, 2;Why it is fastThe filter on trial uses an index, and the series has only ten months.