FAQ: Calculating Churn - Multiple Month: Determine Active Status

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises
Community%20FAQs%20on%20Codecademy%20Exercises1000×208 141 KB

This community-built FAQ covers the “Multiple Month: Determine Active 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 Active 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 (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!

2

I’m stuck on this - I can’t move on because a solution isn’t provided and the hint isn’t helping me. Could I get some help on this?

3

Parenthesis placement is a make-or-break on this one. Did you get any help yet @bytecoder05612?

5

I usually get confused with the ‘’ outside words, e.g. ‘user_id’. I don’t have a clear strategy for when to use them. I mean, sometimes they seemed to be used in table names, some other times in column names, and always on value names. Is there a rule for when to use them, or this is something that has to be memorized? Thank you.

6

Hi there !
Sorry @c930586 I can’t answer your question.
I have another one though, I am really confuse with the use of “WITH”.
Can someone explain when/where to use it ?
It seems that I’ve tried to place it too much in that exercise… Thanks for your help !

7

Well, this thread is not too active…sorry to see its just us…lol…

FWIW, so far anyway, I have seen where the “WITH” is used to make what Codecademy calls a “Temporary Table”. (let me ask for a URL to docs on this? Googling brings too many false pos’s)

So, something like:

WITH months AS

From online:

The SQL WITH clause allows you to give a sub-query block a name which can be referenced in several places within the main SQL query.

I am here to ask what columns you see at the end of the previous step.

My cross join resulted in columns:

id
subscription_start
subscription_end
first_day
last_day

==> I see only three columns at this step. Why?

1 Like
8

No, I see a column “month” on this Step…but you will note that that is not one of the 5 columns that I did get…and I ran the solution to confirm.

I will poke about…

9

Thank you for the explanation of WITH ! I think I got it now !

I am not sure what is your problem…
At the end of " 5.Multiple Month: Cross Join Months and Users", you get the 5 columns
id
subscription_start
subscription_end
first_day
last_day

At the end of “6. Multiple Month: Determine Active Status”, you should get 3 columns
id
month
is_active

Is it not what you get ?

10

Good news on the With Front. :0) I did get past my issue. I have not had a bigger problem than a missing character…but I complained about something in error: my code was not “failing silently”…it was “succeeding silently”. I left off the FROM and the code, I believe, runs without issue…it just does nothing. So, watch for that too.

Honestly, all this SQL caught me off-guard, but it is quite cool.

I was, and am, expecting (and hoping ;0) to work with Python.

Good luck, and feel free to reach out.

11

On 6.) I was having the biggest issue troubleshooting. My problem involved the parentheses of my AND/OR statement.

WHEN (this < that)
AND (this > that)
OR (this = that)

Did not work. Instead, I had to use

WHEN (this < that)
AND (this > that 
OR this =that)

The code seemed to work fine both ways.

13

I was stucked here. If helps you, my error was the CASE statement:

I forgot that this column should be named at the end, like so:

CASE
WHEN (subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END as is_active
FROM cross_join

I was writing it at the beginning.

1 Like
14

Hi, I’ve a question here about CASE statement:
status AS
(SELECT id,
first_day AS month,
CASE
WHEN (subscription_start < month) AND (subscription_end >= month OR subscription_end IS NULL) THEN 1
ELSE 0
END AS is_active
FROM cross_join
)

As you see, When I use ‘month’ in CASE statement , no result shows. But When I use 'first day ’ replace ‘month’, the result shows correct. I think both these should be correct.

15

I tried again. I replace ‘month’ as ‘first day’ at months table and use ‘month’ directly in status table. It worked. So it means in SELECT statement, CASE statement runs first relative to others.

16

I am stuck on the WITH clause. My understanding is:

WITH temp_table_name AS
(SELECT column_1 FROM table,
SELECT column_2 FROM table),
SELECT *
FROM temp_table_name;

Is this right?

17

I figured it out. My confusion was that were just adding “status” to the existing WITH clause. Also there should not be a comma at the end of the with clause before SELECT.

1 Like
20

I’m having the same problem. The instructions tell me I should have 3 columns going into Step 6, but I have 5 columns. However, I was allowed to pass Step 5 with a correct answer, so I’m not sure where my mistake is?

(I made sure FROM statement is present.)

21

When you do your “status AS” statement, don’t forget to bring in id, first day AS month and then add in your CASE argument. This is what hung me up.

From there, I would use the < and > signs only this will give you a better return given we are using a modified result from the months table compared to before where we were using dates.

22

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),
– Add status temporary table here
status AS
(SELECT id, first_day,
CASE
WHEN (subscription_start < first_day) AND
( subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
END AS ‘is_active’
FROM cross_join)

SELECT *
FROM status
LIMIT 100;

Hope it helps. I was stuck on this for like 20 mins :smiley:

9 Likes
23

did you figure this out? I had the same thing as you but I keep getting a parenthesis error.

24

Idea why it doesn’t run?

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 cross_join.id, cross_join.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
)
SELECT *
FROM status
LIMIT 100;