CODEWITHSUNDEEP
sqlpostgresql
xterminator
xterminator

Reputation: 182

Calculating monthly churn

I am trying to calculate a monthly churn rate (for a given month: number_of_people_who_unsubscribed / number_of_subscribers_at_beginning_of_month).

I have a subscribers table that looks like this:

id start_date end_date
1 2020-03-17 null
2 2020-06-21 2020-09-03

I can calculate churn for a single month with a query like this:

select
(
  /* Subscriptions that ended during January */
  select count(*)::decimal from subscriptions
  where end_date is not null
  and end_date >= '2021-01-01'
  and end_date <= '2021-01-31'
) /
(
  /* Subscriptions that were active at the beginning of January */
  select count(*)::decimal from subscriptions
  where end_date is null
  or end_date >= '2021-01-01'
) as churn

That gives me a single percentage of users who unsubscribed during January. However, I'd like to output this percentage for every month, so I can display it as a line chart. I'm not sure where to start - it feels like I need to loop and run the query for each month but that feels wrong. How could I make the same calculation, but without specifying the month manually? We can assume that there is at least one start_date and one end_date per month, so some kind of group by might work.

Ultimately I'm looking for an output that looks something like:

month churn
2020-03 0.076
2020-04 0.081
2020-05 0.062

Upvotes: 0

Views: 1132

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Using your data logic and a sequence of months:

select to_char(running_month, 'yyyy-mm') as "month",
(
  /* Subscriptions that ended during January */
  select count(*)::numeric from subscriptions
  where end_date is not null
  and end_date >= running_month
  and end_date <= running_month + interval '1 month - 1 day'
) /
(
  /* Subscriptions that were active at the beginning of January */
  select count(*)::numeric from listings
  where end_date is null
  or end_date >= running_month
) as churn
from generate_series ('2020-01-01'::date, '2020-12-01'::date, interval '1 month') as running_month;

Upvotes: 1

Related Questions