FAQ: Calculating Churn - Calculating Churn Review

This community-built FAQ covers the “Calculating Churn Review” exercise from the lesson “Calculating Churn”.

Paths and Courses
This exercise can be found in the following Codecademy content:

FAQs on the exercise Calculating Churn Review

There are currently no frequently asked questions associated with this exercise – that’s where you come in! You can contribute to this section by offering your own questions, answers, or clarifications on this exercise. Ask or answer a question by clicking reply (reply) below.

If you’ve had an “aha” moment about the concepts, formatting, syntax, or anything else with this exercise, consider sharing those insights! Teaching others and answering their questions is one of the best ways to learn and stay sharp.

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

Hi,
I was wondering how to create an is_active status by department(segment). I was able to make the is_active, is canceled columns but cant figure out how to do it by department without making the whole thing crash.

Thanks!

I noticed at the end of this lesson that I got slightly different churn rates for each month than what the final solution has. The reason turned out to be that we defined is_active differently.

The solution suggests

case
  when subscription_start < first_day
    and (subscription_end > first_day
     or subscription_end is null)
    then 1
    else 0
  end as is_active

while I used

case
  when subscription_start < first_day
    and (subscription_end >= first_day
     or subscription_end is null)
    then 1
    else 0
  end as is_active

Should we not include subscription_end = first_day to count as active at the start of the month? Considering is_cancelled is defined by

case
  when subscription_end between first_day and last_day
    then 1
    else 0
  end as is_cancelled

Would this not mean that a person cancelling their subscription on the first day of the month will be registered as a cancellation in the month (is_cancelled = 1), but not active (is_active = 0)? That makes very little logical sense to me., but perhaps I am missing something?

1 Like

Why does at the top of the code you use a WITH statement, but then creating additional temporary tables you don’t use it?

It seems that SQL only requires you use the WITH statement once if you’re creating a series of ‘temporary tables’ separated by commas - maybe it’s for convenience or simplicity sake? It would be slightly cumbersome to type 'WITH table1 AS (…), WITH table2 AS (…), WITH table3 AS (…), etc. rather than just going WITH table1 AS (…), table 2 AS (…)

2 Likes

I’ve calculated the churn rate as 1.0* canceled/ 1.0* active, the answer is the same as the one given by the system, however it is not valid, why?

Hi team,

Just have a small question about the churn rate formula itself.
In the beginning of the lecture, the churn rate is basically the number of subscribers that cancelled divided by the total number of subscriber in a period.

But, towards the end of the project, the monthly churn rate is calculated by ‘cancel divided by active’. As per my understanding, active subscribers do not represent the total subscribers in that period. Instead, it represents some portion of the total subscribers.

Is this logic correct? if no, why not?

Regards,
Jim

I’m not sure I understand your question, but I think active subscribers do in fact represent the total subscribers in that period. That period being one month. Total subscribers in any of the tables would represent any subscriber active for any of the months covered by the original subscriptions table.

I don’t believe you could start and end a subscription on the same day. So there should be no records where a person canceled on the first day but was inactive. They will have a start date before that day, maybe the first day of the previous month. In that case, they will be inactive at the start of the previous month, but active for the month of cancelation. This query is specifically designed for one month minimum subscriptions. I’m guessing that to allow for less than one month subscriptions, monthly churn rate would have to be defined slightly differently.

Can anyone tell me why this doesn’t return an answer?

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
)
SELECT *
FROM status
LIMIT 50

Try changing quotation marks. I replaced yours with ’ and worked (also added ‘;’ at the end.)

WOW! Thanks. So when I cut and pasted the code from Github, somehow the format of the quotations changed. It’s obvious now, because the Codecademy workspace shows the dates in red (as integers or dates I suppose) as opposed to yellow where it’s treated more like a string. If I was trying to copy code just into sqlite or something, I don’t know how I’d catch that. Valuable lesson. Thanks again.

I came here to write the same thing - I think your implementation is correct. In the final answer you can actually find examples in the table status where is_active=0 and is_cancelled = 1, and this doesn’t make sense since the churn rate is the number of active users in that month who cancel in that month, divided by the number of active users in that month.

Conversely, if you could have is_active = 0 and is_cancelled = 1, then it would be possible to have a churn_rate above 1. For instance, with 2 users that cancel but only one that is active you’d have churn_rate = 2 / 1 = 200%, which is nonsensical.