User Churn

– create temporary table months
with months as
(
select
‘2017.01.01’ as first_day,
‘2017.01.31’ as last_day
union
select
‘2017.02.01’ as first_day,
‘2017.02.28’ as last_day
union
select
‘2017.03.01’ as first_day,
‘2017.03.31’ as last_day
),

–Cross join months and subscriptions to see subscriptions per month
temp_table as (
select *
from subscriptions
cross join months
),

–set status of each id in different segments
status as (
select id,
first_day as month,
case when segment = 87 and
(subscription_start < first_day)
and (subscription_end > first_day
or subscription_end is null)
then 1
else 0 end as is_active_87,
case when segment = 30 and
(subscription_start < first_day)
and (subscription_end > first_day
or subscription_end is null)
then 1
else 0 end as is_active_30,
case when segment = 87 and
(subscription_end
between first_day and last_Day)
then 1
else 0 end as is_cancelled_87,
case when segment = 30 and
(subscription_end
between first_day and last_Day)
then 1
else 0 end as is_cancelled_30
from temp_table),
–get a total of the acive and inactive
status_aggregate as (
select month,
sum(is_active_87) as sum_active_87,
sum(is_active_30) as sum_active_30,
sum(is_cancelled_87) as sum_cancelled_87,
sum(is_cancelled_30) as sum_cancelled_30
from status
group by month)

–workout the churn rate for each segment
SELECT month
,1.0 * sum_cancelled_87/sum_active_87 as churn_rate_87
,1.0 * sum_cancelled_30/sum_active_30 AS churn_rate_30
FROM status_aggregate;