This community-built FAQ covers the “Single Month I” 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 I
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!
Is it completely necessary to include the end parentheses of the previous statement and the beginning parentheses of the next statement on the same line as the mathematical symbol (e.g. “) / (”)? Is there a way for me to include the mathematical symbol by itself on a line of code to allow myself to visualize the code better?
Hi there, I want to ask question regarding the condition in denominator (total active users in specific month)
WHERE subscription_start < ‘2016-12-01’ AND (
** (subscription_end >= ‘2016-12-01’)**
** OR (subscription_end IS NULL)**
if I remove those codes the result will be similar with and without those codes. I just curious, whats the reason to put those code?
Can anyone explain why we skip a line after the parentheses (second SELECT statement) in this code for calculating customer churn? Also it says at the beginning that there is not supposed to be any drop out on the first month as the policy is to stick to membership for at least a month. What is the use of this piece of information? Or is it just there to test/confuse us?
The second line of code includes all subscriptions that have ended after 2017-01-01 (including February 2019 and March 2019). This allows the code to calculate all active users as of 2017-01-01 (subscription start < ‘2017-01-01’).
The first line of code calculated all users that ended their subscriptions in January. This calculation does not belong in the denominator (churn = cancellation / total subscribers).
Answered my own question. We include the parentheses around the division sign to ensure that the division sign is not left alone in a coding line. That will definitely screw up the code, as SQL interprets “/” as it’s own line of code, rather than part of a formula.
I came here with the same question, but I think I answered it. In the example given, the AND condition is redundant and unnecessary since it is stated in the problem that there are no cancellations in the first month of the service (December 2016). However, the AND condition is important in the general case of calculating churn, since we don’t want to include subscribers who canceled before the beginning of the month.
This would probably be clearer if the question had us calculate churn for February instead.
select 1.0 *
(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end between ‘2017-01-01’ and ‘2017-01-31’)
)
/
(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (
(subscription_end >= ‘2017-01-01’) or
(subscription_end is null)
)
)
as result;
The result is not coming
it is showing error in line 9
and answer is also wrong as 35 .
and when i readjusts the brackets as in below case
select 1.0 *
(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end between ‘2017-01-01’ and ‘2017-01-31’)
) /
(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (
(subscription_end >= ‘2017-01-01’) or
(subscription_end is null)
)
)
as result;
The answer seems fine.
Can somebody explain this . Is there any issue in the code.
In the churning example let’say for December, I am confused by the way the denominator (defined as active users at the beginning of December) is calculated.
In particular, as active users, are included also the customers that cancelled the subscription at the 1st of December as the code is:
…AND (
(subscription_end >= ‘2016-12-01’)…
I wonder, why is the equal sign (=) is used here?
A user who cancels his/her subscription in the 1st day of the month, is he/she supposed to be active that day and consequently that month?
Is after all this calculation, just a techical detail which is related to the exact time each company counts active users for each day?
I mean , if it regards as active users for the 1st of December those who were subscribed until the very end of the that day, i.e. time 23:59, then I think that the code should not include the (=) sign, after the “subscription_end”. The users who ended the the subscription during 1 December, are no longer active as the calculation is made at the end of the day.
However, if the company counts them at the very beginning of day 1st December , e.g. time 00:00 (between 30 November and 1 December), then the (=) sign is correctly written in the code.
codeacademy make it very difficult to understand , denominatior:
the code that is right following lesson example
select 1.0*
(select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end between ‘2017-01-01’ and ‘2017-01-31’)
)/(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end is null or subscription_end >= ‘2017-01-01’
or subscription_end <=‘2017-01-31’))
as result;
question:
AND (
(subscription_end >= ‘2017-01-01’) WHY not include ‘<= 2017-01-31’, u r going to include 2018-2019 data??
OR (subscription_end IS NULL /WHY?: include all before and after 1/2017)
Total suber is calculated until beginning of the month right?
codeacademy make it very difficult to understand , denominatior:
the code that is right following lesson example
select 1.0*
(select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end between ‘2017-01-01’ and ‘2017-01-31’)
)/(
select count()
from subscriptions
where subscription_start < ‘2017-01-01’
and (subscription_end is null or subscription_end >= ‘2017-01-01’
or subscription_end <=‘2017-01-31’))
as result;
question:
AND (
(subscription_end >= ‘2017-01-01’) WHY not include ‘<= 2017-01-31’, u r going to include 2018-2019 data??
OR (subscription_end IS NULL /WHY?: include all before and after 1/2017)
Total suber is calculated until beginning of the month right?
We want to calculate churn for active users in January 2017. Future users would be included in the denominator indefinitely, when we only want to include users that were active in January 2017 (New users in February and March are not active in January 2017). The problem states that users cannot cancel in the first month, so we do not have to worry about new users within December 2016 being excluded from the denominator. I hope this helps.
Hi everyone! For the WHERE query, when subscription_start < ‘2017-01-01’ , it doesn’t include 1-Jan-2017. Shouldn’t we include it since we are looking for the churn rate of Jan 2017?
Hi! I keep getting 0.039 for my answer and I’m having trouble finding the bug. I’ve checked to ensure that all the operands match the answer and quadruple checked my dates. Am I missing something?
SELECT 1.0 *
(SELECT COUNT(id)
FROM subscriptions
WHERE
(subscription_start < ‘2017-01-01’)
AND
(subscription_end
BETWEEN ‘2017-01-01’
AND ‘2017-01-31’)
)/(
SELECT COUNT(id)
FROM subscriptions
WHERE
(subscription_start < ‘2017-01-01’)
AND
(subscription_end >= ‘2017-01-01’)
OR
(subscription_end IS NULL)
)
AS Result;
Okay, I am completely lost and have no clue what to do on this one. I would like to keep my steak going, but I don’t even understand what they are asking for.
There’s not much support for SQL here on the forums unfortunately, but if spacing doesn’t matter in SQL, I can’t grasp why the first query doesn’t lead to anything and the second one gets the desired output???
Doesn’t execute; no errors:
SELECT 1.0 *
(SELECT COUNT(id)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (subscription_end >= '2017-01-01'
AND subscription_end < '2017-02-01'))
/
(SELECT COUNT(id)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND ((subscription_end >= '2017-01-01') OR (
subscription_end IS NULL)))
AS result;
Works fine:
SELECT 1.0 *
(SELECT COUNT(id)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND (subscription_end >= '2017-01-01'
AND subscription_end < '2017-02-01'))
/ (SELECT COUNT(id)
FROM subscriptions
WHERE subscription_start < '2017-01-01'
AND ((subscription_end >= '2017-01-01') OR (
subscription_end IS NULL)))
AS result;
Only difference is that the division sign is now on the same line as the denominator (works also with numerator).
) below.
) to up-vote the contribution!