User Churn - Calculating Churn Rates

I’m in the Data Science course currently and am having problems on solving number 8 the User Churn - Calculating Churn Rates project. I have already viewed the video in the “Get Help” section for help.
My code is exactly like that of the video, but it still won’t run? Not sure why.

  1. Calculate the churn rates for the two segments over the three month period. Which segment has a lower churn rate?

My whole code is:

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) /* havent unsubscribed yet /
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) /
havent unsubscribed yet */
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 sum_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;

HELP PLEASE. I’m trying to figure it out and I’m not sure what’s wrong with it because for the questions previous to it, it runs perfectly fine. The “Get Help” video also says that it has been “DEPRECATED”.

@catlbui,

Welcome to the forums!

Did you go back and try to add in comments? Because based on the code you posted, this whole section is commented out:

Remember, /* starts a multi-line comment and */ ends the multi-line comment.

I have a similar issue with the following 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 (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 is null) or (subscription_end>first_day)))
then 1 else 0 end as ‘is_active_30’,

case when (segment = ‘87’) and (subcription_end between first_day and last_day) then 1 else 0
end as ‘is_canceled_87’,

case when (segment = ‘30’) and (subcription_end between first_day and last_day) then 1 else 0
end as ‘is_canceled_30’
from cross_join)

status_aggregate as (id, month,
sum(is_active_87) as sumactive87, sum(is_active_30) as sumactive30, sum(is_canceled_87) as sumcanceled87, sum(is_canceled_30) as sumcanceled30
from status
group by month)
select month,
1.0 * sumcanceled30/sumactive30,
1.0 * sumcanceled87/sumactive87
from status_aggregate;

Unable to figure out what is wrong with this code.

@blog9000384386,

Make sure you have a comma between your status CTE and your status_aggregate CTE.

1 Like

I am also facing the same issue

in addition to the comments issue was mentioned here, you can’t execute this query because your last SELECT clause is not familiar with the ‘month’ column since you haven’t defined it on the ‘status_aggregate’ temp table.
you should modify it

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

Alternatively you can just remove the month from the table and the SELECT/GROUP BY clauses of course.