This community-built FAQ covers the “Multiple Month: Determine Cancellation Status” 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: Determine Cancellation Status
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!
Make sure you spell canceled with one L to get this one marked correct. Got stuck for a little while until I learned that you can’t use the alternate spelling, cancelled.
SELECT column_name,
CASE
WHEN condition THEN ‘Result_1’
WHEN condition THEN ‘Result_2’
ELSE ‘Result_3’
END
FROM table_name;
CASE statements are used to create different outputs (usually in the SELECT statement). It is SQL’s way of
handling if-then logic.
The prior exercises utilized the select portion and I tried to incorporate what I had learned in them.
Codecademy should really be better with its hints or error notifications (I got the same errors notices as chahn1138). I struggled with this exercise for over an hour.
Maybe say which parts we are not adding or should consider adding or which sequences would not be needed.
Now that I know there is the possibility of building blocks (like a SELECT statement) not always needed I’ll try more variety in my coding if I can.
I tried to avoid going to the solution, yet maybe I will have to resort to the solution more often if it helps me wrap my head around exercises and saves some time when I am stumped.
The whole “Calculating Churn” track seems pretty challenging and wordy, I did fine with the exercises before but here it got really challenging really quickly … wish there was a video explanation for it.
Really helpful! Thanks! I was worried that it had jumped in difficulty - and the hints weren’t helping! Think I clicked on ‘Get Solution’ most of the way through!
You should include only BETWEEN in your CASE statement.
I did this and it worked
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 100;
Is there any difference between if i write
WHEN subscription_end BETWEEN ‘2017-01-01’ AND ‘2017-03-31’
in place of
WHEN subscription_end BETWEEN first_day AND last_day
??
There sure is; in the first scenario you’re comparing static data for first_day and last_day, which is not what we want. Those are variables just like subscription_end, and should be dynamic as we parse rows of a table. Your second scenario accomplishes this.
Why does in the answer, they have given as “subscription_end > first_day” ?
shouldn’t it be “subscription_end >= first_day” ?
please can anyone explain as to why codecademy omitted "= " sign?
if subscription_start = ‘2016-12-01’ and if there is one month commitment what should be the subscription end_date ? should it be ‘2017-01-01’. or ‘2016-12-31’ ?
Solution provided(condensed to the is_canceled portion
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 100;
and this:
*CASE *
WHEN (subscription_end BETWEEN first_day AND last_day OR*
subscription_end IS NOT NULL) *
THEN 1*
ELSE 0*
END as is_canceled
FROM cross_join)
The second query is what I came up with and it appears to return the same results. I realize after seeing the solution that the second argument for IS NOT NULL would be irrelevant as either a subscription ended between the first day and last day or it didn’t, but it does not at least in my mind make my query wrong just bloated as it were.
I am having issues with getting my query to run anything. I’ve watched the walk-through video to gain insight on my coding errors and compared for accuracy. However, no matter what, my query does not return any results and doesn’t provide any errors.
I believe I simply need a second pair of eyes. Is there anywhere I can get one-on-one assistance to take a look without posting my whole code on here?
Submit to the FORUM for group think and clarification. It’s good for the group to look through someone else’s code too as errors in your “own code” don’t always pop out.
Hey I thought it should be >= too but have not found anything which shows it should be just >
Most likely a fault? Did you find out why this was the case at all?
) below.
) to up-vote the contribution!
