This community-built FAQ covers the “Single Month II” exercise from the lesson “Calculating Churn”.
Paths and Courses
This exercise can be found in the following Codecademy content:
FAQs on the exercise Single Month II
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 (
) 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 () below!
Agree with a comment or answer? 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!
I’m confused as to why the first code block in the instructions is necessary (creating a temporary table called enrollments), since the same criteria are used in the third code block (the is_active column). Without it, I still get the right answer. Is there anything wrong with doing it this way?
WITH status AS
(SELECT
CASE
WHEN ((subscription_end > ‘2017-01-31’)
OR (subscription_end IS NULL))THEN 0
ELSE 1
END AS is_canceled,
CASE
WHEN subscription_start < ‘2017-01-01’
AND ((subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)) THEN 1
ELSE 0
END AS is_active
FROM subscriptions)
SELECT 1.0 * SUM(is_canceled) / SUM(is_active)
AS churn_rate
FROM status;
12 Likes
I had the same question in mind. This is much simpler and easy to comprehend. You might be missing one parenthesis (closing for WITH) otherwise great job!
2 Likes
Thanks, @szabeen ! I’m still curious if anyone will come forth with an explanation for why the enrollments temporary table is necessary (if it is…?) We’ll see 
The enrollments table is just another way of getting active users from subscriptions table, you can calculate churn rate by enrollments and status table containing is_canceled column or by status table containing is_canceled and is_active columns. In the second case, the status.is_active table is used instead of enrollments table. The course makes you write both tables so you remember both of these methods
4 Likes
enrollment limited the data from 1/1 to 1/31. subscriptions has data for several months. For example, subscription_end = 12/2/2016 will return as 1 in the case is_canceled without creating enrollment table and it should not be included in calculating 1/2017 churn rate
2 Likes
@board5027732213 Thanks for responding! I don’t think that’s what the instructions are saying, though. The 4 code blocks in the instructions comprise a single query. You can tell because the first block defining enrollments ends with a comma, and the second one doesn’t start with WITH. What seems weird to me is that, although they say that part of the goal here is to avoid repeating conditional statements, the temporary enrollments table uses the same conditional statements as the is_active column of the status table.
1 Like
Hey @java7682825630 thanks for responding! Sorry for editing multiple times… So, when I bring up the status table, I see a row with a subscription_start of 12/2/2016 that has a 1 in the is_active column, but I don’t see one with a subscription_end of that date. It appears that the earliest subscription_end date is in 2017. Without the enrollments table, the status table still includes rows for which both is_active and is_canceled are 0, which is fine because they won’t be added in to the churn calculation because they have a value of 0. Unless I’m misunderstanding what you’re saying?
I’m struggling with understanding WITH and CASE statements and can’t seem to get them correct. Can someone tell me where I’m going wrong with my query for this exercise?
WITH enrollment AS
(SELECT *
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2016-12-01’)
OR (subscription_end IS NULL)
)),
status AS
(SELECT
CASE
WHEN (subscription_end > ‘2017-01-01’)
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_cancelled),
status AS
(CASE
WHEN subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
) THEN 1
ELSE 0
END as is_active
FROM enrollments
)
SELECT 1.0 * SUM(is_canceled) / SUM(is_active)
FROM status;
When I run this, nothing happens.
2 Likes
I’m having the same issue. The example says to name both middle columns as status yet when you try to run the code is says “Error: near line 1: duplicate WITH table name: status”
This is the code I used:
WITH enrollments AS
(SELECT *
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND ((subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
)),
status AS
(SELECT
CASE
WHEN (subscription_end > ‘2017-01-01’)
OR (subscription_end IS NULL) THEN 0
ELSE 1
END AS is_canceled),
status AS
(SELECT
CASE
WHEN subscription_start < ‘2017-01-01’
AND ((subscription_end > ‘2017-01-01’)
OR (subscription_end IS NULL)) THEN 1
ELSE 0
END AS is_active
FROM enrollments),
SELECT 1.0 * sum(is_canceled) / SUM(is_active)
FROM status;
Hello! I think your problem is with the word “enrollment”… When you first write it in the WITH statement it has no ‘s’ but when you call it towards the end, you add an ‘s.’ Hope this helps!
Hello! You actually don’t rename the table for multiple CASEs. Remember in the CASE lesson how we learned that we can have multiple CASE statements before we end? That is what is happening here. Just get rid of the second ‘status AS’ and ‘SELECT’ statements and you should be fine.
4 Likes
@cssslayer88813, also see my other comment for @byte7181295660 as you have the same problem
I post here the solution to which I succesfully arrived. To put you on context, I tried employing a double “WITH” structure but could not manage to properly define both “enrollments” and “status” temporal tables at the same time (with a single “WITH” clause, or even two “WITH” clauses together). So I thought about the problem with another approach. Basically, “enrollements” is the only table that I defined with the “WITH” sentence. I defined both the “is_cancelled” and “is_active” columns with a “CASE” embedded in the final “SELECT” sentence, recalling the temporary table “enrollments”. Hope it serves you well:
WITH enrollments AS
(SELECT *
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
))
SELECT 1.0 * SUM(is_canceled) / SUM(is_active)
FROM (SELECT
CASE
WHEN (subscription_end > ‘2017-01-31’)
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_canceled,
CASE
WHEN subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)) THEN 1
ELSE 0
END as is_active
FROM enrollments
) AS status;
2 Likes
I got the same question that why do we need enrollment block here. After reading these discussion, I still don’t know why. It seems it works also fine and makes more sense without enrollment block.
1 Like
why this line in status table mean active user?
CASE
WHEN subscription_end >= ‘2017-01-01’ (why this line mean active user?)
OR
subscription_end IS NULL
THEN 1
ELSE 0
END as is_active
FROM enrollments
2 Likes
I couldn’t get the answer for this myself 
I have many doubts … it says:
Error: near line 1: near “SELECT”: syntax error
my code below:
WITH enrollment AS
SELECT *
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’ AND
subscription_end >= ‘2017-01-31’ OR subscription_end IS NULL,
status AS
SELECT
CASE
WHEN subscription_end < ‘2017-01-31’ OR subscription_end IS NULL THEN 0
ELSE 1
END AS is_canceled,
CASE
WHEN subscription_start < ‘2017-01-01’ AND
subscription_end >= ‘2017-01-31’ OR subscription_end IS NULL THEN 1
ELSE 0
END AS is_active
SELECT 1.0 * SUM(is_canceled) / SUM(is_active)
FROM status;
I am trying to do it by another way, which skip the step of “WITH enrollments AS”, but I cannot get the correct result. Can someone help me? THX
WITH status AS
(
SELECT
CASE
WHEN subscription_end <2017-01-01
AND subscription_end > 2017-01-31
OR subscription_end IS NULL
THEN 0
ELSE 1
END as is_canceled,
CASE
WHEN subscription_start<2017-01-01
AND subscription_end>= 2017-01-01
OR subscription_end IS NULL
THEN 1
ELSE 0
END as is_active
FROM subscriptions)
SELECT 1.0 * SUM(is_canceled)/SUM(is_active)
FROM status;
I’m trying a different method without WITH clause, but there is no result showing up.
Would you please let me know what I have written wrongly?
My code is as below;
SELECT 1.0*
SUM(CASE
WHEN (subscription_end > ‘2017-01-31’)
OR (subscription_end IS NULL) THEN 0 ELSE 1
END)
/
SUM(CASE
WHEN subscription_start<‘2017-01-01’
AND ((subscription_end>=‘2017-01-01’) or (subscription_end is null))
THEN 1 ELSE 0
END)
FROM subscriptions;
I also have no idea why we can’t just do the CASE statements on the initial subscription table rather than first building the enrollments table. It seems like duplication for no reason.
