# FAQ: Calculating Churn - Multiple Month: Churn Rate Calculation

Hi! Could you share the link with me to the video? I don’t understand/know why we need to add the (segment = 87) and other similar parts…which was not mentioned in any of the previous steps.

Here’s my code which won’t run:
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 (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END as is_active,
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
END as is_canceled
FROM cross_join),
status_aggregate AS
(SELECT
month,
SUM(is_active) as active,
SUM(is_canceled) as canceled,
FROM status
GROUP BY month)
SELECT month, 1.0* SUM(status_aggregate.canceled)/SUM(status_aggregate.active) AS churn_rate
FROM status_aggregate
GROUP BY month;

Thanks!!

1 Like

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 (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 month,
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.0sum_canceled_87 /sum_active_87 as churn_rate_87,
1.0
sum_canceled_30 /sum_active_30 as churn_rate_30

FROM status_aggregate;

select min(subscription_start), max(subscription_end)
from subscriptions;

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-30’ AS last_day
),
cross_join as(
select *
from months cross join subscriptions
),

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 month,
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 * sum_canceled_87 / sum_active_87 as churn_87,
1.0 * sum_canceled_30 / sum_active_30 as churn_30
from status_aggregate;

1 Like

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 (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END as is_active,
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
END as is_canceled
FROM cross_join),

status_aggregate AS (
SELECT month, SUM(is_active) AS active, SUM(is_canceled) AS canceled
FROM status
GROUP BY month)

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

1 Like

Hi,
I have been stucked with this by making it over complicated. Here is what i’ve typed originally :
SELECT

• status_aggregate.month AS month,*
• 1.0 * (status_aggregate.is_canceled/status_aggregate.is_active) AS churn_rate;*
Why this didn’t work while the following did ?
SELECT month,
• 1.0 * canceled/active AS churn_rate*
FROM status_aggregate;

I had this same question and then figured it out.

From what I can gather, if you are selecting FROM a table you do not also need to include the .TABLE_NAME with the column you are selecting. My guess is because this is redundant.

This churn rate lessons are by far the my toughest challenges since enrolling to the datascience path.

Almost every lesson/instruction seems new and complex. I used the ‘View Solution’ button at almost every stage. I have to use other means to grasp it.

1 Like

hey guys. I tried most of the things from here to fix my code but it didn’t work for me. Then I change my mind to copy codes from here to look at it but it didn’t work also. There is code which I copied from here

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 (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END as is_active,
CASE
WHEN subscription_end BETWEEN first_day AND last_day THEN 1
ELSE 0
END as is_canceled
FROM cross_join),

status_aggregate AS (
SELECT month, SUM(is_active) AS active, SUM(is_canceled) AS canceled
FROM status
GROUP BY month)

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

In the screenshot, you’ll see there is no query result or any fault notification. Can anyone help me?

This is my original code :
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 (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
month,
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 * sum_canceled_87 / sum_active_87 AS ‘Churn 87’,
1.0 * sum_canceled_30 / sum_active_30 AS ‘Churn 30’
FROM status_aggregate
;

Thank u

Hi design,
Thanks for your reply on this - it was bugging me for a while! I suppose what throws me is that (canceled/active) should generate 0 as a result… It’s ultimately not zero or multiplying the result by 1 wouldn’t turn it into a value. Put it this way - it works both ways in any spreadsheet program i’ve ever used. I’m willing to accept “that’s just the way it is” but was wondering if there is a deeper answer that explains the difference?

This is what is called an integer truncation.
The values for cancel and active are integers (1, 2, 3…) and not floats (1.2, 3.5, 2.3…)

What we are doing by first multiplying it by 1.0, is transforming the integer into a float value.

For this example, let’s say that cancelled = 7 and active = 10.

If we divide 7/10, that would give us 0.7. However python is working with two integers and will try to output an integer as well. So it will round it down from 0.7, to 0.
Give it a try with different values, for example 25/9, and you will see python rounding it down to the nearest integer (from 2.7 to 2 in this case).

Now, when we do 1.0 * 7 / 10, it will first become 7.0 / 10. We now have a float (7.0) and an integer (10). As float is more specific, the output will be a float as well, this way we can have 0.7 as the result.

1 Like

Thanks design!! Obvious when you know how… I really hadn’t clocked on to the idea that the calculation was so literal. I’ll have to bear that in mind. Really appreciate you taking the time to explain that one. Makes it so much more explicit why I was wrong/had misinterpreted.

1 Like

Hi, Guys

In the bonus question they ask:

How would you modify this code to support a large number of segments?

and the hint was

Avoid hard coding the segment numbers.

This hard coding means the months table separation on first day and last day ?

Hard coding refers to using date-time strings in place of variables. We might generate a temporary table (like months) that spans a defined range of dates over years, effectively widening our visibility to customer activity.

i supposed to write the code but i have got the number 0 for ‘is_canceled_87’ and ‘is_canceled_30’

what these two column should be?

Hey! After trying several times I found that the answer to the exercise is quite simple. I hope you find it useful:

``````SELECT month,
1.0 * canceled/active AS churn_rate
FROM status_aggregate;
``````
1 Like

Would you mind helping me out
I am stuck on this

status_aggregate AS

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

FROM status)

select *
from status_aggregate
group by month ;

It is not returning what the code desires …

# Multiple Month: Churn Rate Calculation

If you were struggling to solve the User Churn Rate here I give you some advice:

1. DON’T use () multiplying by ‘1.0’, I was confused at first, but then when I make without () it works…I think is just the execution order.

2. Use the ROUND function to make it clear concrete, with few decimal places, I used just 2.

If it still not working, try this:

SELECT month, ROUND (1.0 * canceled/active, 2)as churn_rate
FROM status_aggregate;

Hi!

So I originally put

SELECT month, 1.0 *(status_aggregate.canceled / status_aggregate.active)
AS churn_rate FROM status_aggregate;

and got an error message.

Then I removed the parentheses and is worked but not sure why. Can any one explain.

So I have tried
1.0 * (status_aggregate.canceled/status_aggregate.active) AS churn_rate

to calculate the churn rate, however, the result returns 0 for all rates, then I check the solution it doesn’t have parenthesis. Does anyone know the difference by this parenthesis? Much appreciated!!

Anyone thought of a good answer to the last question: “How would you modify this code to support a large number of segments?” The hint says: avoid hard coding the segment numbers, but how would we actually create this code then with flexible numbers?

2 Likes