FAQ: Calculating Churn - Multiple Month: Churn Rate Calculation

This community-built FAQ covers the “Multiple Month: Churn Rate Calculation” exercise from the lesson “Calculating Churn”.

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

FAQs on the exercise Multiple Month: Churn Rate Calculation

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!

So I tried and tried to solve this one, and ultimately had to hit that dreaded “SOLUTION” button to see the answer. In case you’re like me and are determined to try to solve it on your own, here are some helpful hints to solve this one:

  • Keep this answer as short, simple and stripped down as possible. All of the past statement answers in this lesson were very long and wordy. This one is not.

  • Don’t forget to multiply by 1.0 to make this a float answer. The placement of where you put this 1.0 should be very strategic. Make sure you think about what part of this SELECT statement needs to be multiplied by 1.0!

I hope this helps someone!

12 Likes

I’m displaying here what was the buildup to calculating churn rate. For some reason the code won’t display it. I’ve checked many times with the video. Any help would be appreciated.

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;

Hi @duffmcgillicuddy,

Try this:

SELECT
month,
1.0 * SUM(sum_canceled_87) / SUM(sum_active_87) AS churn_87,
1.0 * SUM(sum_canceled_30) / SUM(sum_active_30) AS churn_30
FROM status_aggregate
GROUP BY month;

NEW QUESTION:
probably obvious, but why do these () break this solution?:
1.0 * (canceled/active) AS churn_rate
as opposed to the correct, working solution:
1.0 * canceled/active AS churn_rate

what am I forgetting, missing, etc, that makes my first solution all calculate to 0.00?

5 Likes

I have the same question. The () issue is what brought me to the thread. Does anyone have an answer yet?

I have the correct solution now, but would like to know why () breaks the solution.

3 Likes

This language is so vague.

Earlier in this lesson the code wouldn’t execute because my division sign was on its own line, here the code wouldn’t execute properly because my *1.0 calculation came after the division instead of before.

Yet there are no explanations for this anywhere in the material (let alone readily available elsewhere online), and no-one cares to actually address queries on the forums.

How important is this language anyway? I’m considering ditching it altogether for the time being.

1 Like

I have the same doubt, what is the problem with the ()?

The reason why it doesn’t work is purely math and priority of calculations.
1.0 * (canceled/active) implies that you are going to first make the statement become 1.0 * (result)… so it will be to late to try to use 1.0, as it will multiply 1.0 * 0, for instance…

in the other scenario, (1.0 * canceled/active), as there is no parenthesis between the operations, it will be executed in reading order: first 1.0 * canceled, then divide the result by active.

This has nothing to do with the language itself, it is just executing on the order that you requested.

7 Likes

I’ve replied a similar question about the * 1.0 problem.
If you request the program to first divide, there will be no point in trying to multiply by 1.0, as the result will be already 0 anyway… the order of operations is important in these cases, and this is math related.

as for the other problems about the position of the division sign, don’t let that hold you back. there are lots of particularities (bugs) on most of the languages and after you face the problem, you will remember how they operate.
Answering your last question, I would suggest you to stick to the language and just understand that some problems like that always come along, no matter what language you choose… do it for you and you won’t regret :slight_smile:

2 Likes

Hey,

Thanks for the reply. Frustration got the better of me while typing it, so my apologies if it sounded a bit too much like a rant. I do understand what went wrong now.

I have noticed that like most programming languages, querying data follows similar patterns across languages (and there’s things in pandas that I struggle with, whereas it’s an easy line of code in SQL). It is annoying that there’s no traceback when things go wrong.

Anyway, thanks again for the explanation!

-Twan

Hey guys, my SQL query isn’t returning any tables or data at all when I run it. I’ve compared it against the sample footage and I swear it’s identical; can anyone spot the error(s)???
It’s driving me up the wall lol

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 susbcription_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 susbcription_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

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

I’m having the same problem, but I’ve found some things with your code:

-END as is_canceled_30 */ at the end of your status temp table

-SELECT * FROM status LIMIT 10; is right before status_aggregate temp table

I can’t seem to get the code below to work. Help please!

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
;

Thanks for that chip - sadly those were errors remaining from me trying to trouble shoot my code (by commenting portions out, and testing which parts work on their own: hence the limit 10). Having removed them I still don’t get any results or columns when I run the code.

I’m pretty sure this portion of my code is the problem:
status AS (
SELECT id,
first_day AS month,
CASE
WHEN (subscription_start < first_day)
AND (subscription_end > first_day
OR susbcription_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 susbcription_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

)

The rest of mine works fine. Is anyone able to spot what I’m doing wrong?

You need to put ’ , ’ at the end of each new ‘as first_day’ alias, like so:
select
‘2017-03-01’ as first_day,
‘2017-03-31’ as last_day

1 Like

Just posting that I got my code to work!!! :smiley:
I just had a few spelling errors (susbcription…) and missed a comma in

Here it is if you want to copy paste it for comparison purposes:
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
;

GOOD LUCK

1 Like

Thanks! That did it!!! I figured out the commas were missing between the temp tables after I posted. :raised_hands:

2 Likes

Ahhh, yes now I see. Thanks for clarifying that :slight_smile:

Hi, where is this video that everyone is referring to?