FAQ: Calculating Churn - Multiple Month: Churn Rate Calculation

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

FROM subscriptions),

cross_join AS

(SELECT subscriptions., months.

FROM subscriptions

CROSS JOIN months),

status AS

(SELECT id,

first_day AS ‘month’,

CASE

WHEN (subscription_start < first_day)

AND (subscription_end > first_day

OR subscription_end IS NULL)

AND segment = 87

THEN 1

ELSE 0

END AS ‘is_active_87’,

CASE

WHEN (subscription_start < first_day)

AND (subscription_end > first_day

OR subscription_end IS NULL)

AND segment = 30

THEN 1

ELSE 0

END AS ‘is_active_30’,

CASE

WHEN (subscription_end BETWEEN first_day AND last_day)

AND segment = 87

THEN 1

ELSE 0

END AS ‘is_canceled_87’,

CASE

WHEN (subscription_end BETWEEN first_day AND last_day)

AND segment = 30

THEN 1

ELSE 0

END AS ‘is_canceled_30’

FROM cross_join),

status_aggregate AS

(SELECT

SUM(is_active_87) AS ‘sum_active_87’,

SUM(is_active_30) AS ‘sum_active_30’,

SUM(is_canceled_87) AS ‘sum_canceled_87’,

SUM(is_canceled_30) AS ‘is_canceled_30’

FROM status

GROUP BY month)

SELECT month,

1.0 * sum_canceled_87/sum_active_87 AS ‘churn_rate_87’,

1.0 * sum_canceled_30/sum_active_30 AS ‘churn_rate_30’

FROM status_aggregate;

I banged my head behind this for a few hours. Then I realized I was doing the usual newbie mistake of focusing on the question entirely without reading over the previously written statments(code).

I would love to give the answer as its the same as clicking the solution button, but instead before you click the solution— have these quick tips:

1) The solution is actually there in the instructions.
2) Pay attention to the previously written statements where you calculate the sums of is_active and is_canceled. It aids in understanding the status_aggregate.active and status_aggregate.canceled.
3) The `1.0 * ___` placement is really simple .

If the tips dont work

``````SELECT cake, 1.0 * nobutter/butter as baked_goods
FROM oven;
``````

Happy learning

hey…thanks for taking the time to answer this

I need help understanding a particular condition while solving the churn calculation problem.
When we do this
WHEN (subscription_start < first_day
and ((subscription_end > first_day)
or (subscription_end is null)))
and (segment = 87), we do not count the first day anywhere. Why not this - WHEN (subscription_start < first_day
and ((subscription_end >= first_day)
or (subscription_end is null)))
and (segment = 87)
Why can’t subscription be canceled on the first_day?

I’ve done that one and it’s working.

SELECT month,
1.0 * status_aggregate.canceled/status_aggregate.active as churn_rate
FROM status_aggregate;

spelling of subscription in status table

I have a problem with my code. It won’t run when I pressed save. I do not know why, can anybody help me?

SELECT MAX(subscription_start), MIN (subscription_start) FROM subscriptions AS min_max_start;

SELECT MAX(subscription_end), MIN (subscription_end) FROM subscriptions AS min_max_end;

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 AS (

SELECT * FROM subscriptions

CROSS JOIN months

),

status AS (

SELECT id, first_day AS ‘month’,

CASE

WHEN (segment = 87 AND subscription_start < first_day AND (subscription_start > 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_start > 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_canceled_87’,

CASE

WHEN (segment = 30 AND subscription_end BETWEEN first_day AND last_day ) THEN 1

ELSE 0

END AS ‘is_canceled_30’

),

status_aggregate AS (

SELECT SUM(is_active_87) AS ‘sum_active_87’,SUM(is_active_30) AS ‘sum_active_30’, SUM(is_canceled_87) AS ‘sum_canceled_87’, SUM(is_canceled_30) AS ‘sum_canceled_30’ FROM status

GROUP BY month

)

SELECT month, 1.0 * is_canceled_87 / is_active_87, 1.0 * is_canceled_30 / is_active_30 FROM status_aggregate;