FAQ: Calculating Churn - Multiple Month: Determine Active Status

I have almost identical code as you, let me know if you find out the answer

I was wondering what the difference is, by adding a WITH and not adding a WITH in the code, such as the ‘status’ and ‘cross_join’ section, compared to using WITH in the ‘months’ section

Seems like you are missing the “FROM cross_join” part after the SELECT statement ?

First, there’s a problem is in your use of cross_join.id, cross_join.first_day AS month in status.

When you don’t use joins, as the case is with status table, you don’t need to refer to different tables in the SELECT part of the code.

To elaborate, the join was made between months and subscriptions creating a “final” table called cross_join.

Then you go on to create another table called status - but in this case you query from only ONE table, which is cross_join.

Second, you forgot to add FROM cross_join in status.

Third, and I find it to be crucial at times (can mess up your calculations), the misplacement of parentheses when having AND + OR.

Added the answer, hope it helps.

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 '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'
 FROM cross_join
)

SELECT *
FROM status;
7 Likes

When we want to create multiple temp tables using WITH, we write it once in the first table.

After we finish with it we close the parentheses and add , that says another use of WITH is about to come.

3 Likes

Hi, for below code
‘’’

(subscription_start < first_day)
AND (
subscription_end > first_day
OR subscription_end IS NULL
) THEN 1
ELSE 0
‘’’

Shouldn’t subscription_end > last day, as subscription should end after month instead of
first_day.

I am confused :confused:.
Please help :pray:

I got the solution :smiley: , since subscription starts after the first day itself,
Any subscription active after first day will be active for that month.
IF one does subscription end > last day, then that subsccription will count for next month.

Thank you so much! I was stuck on this forever. I forgot the ‘’ in ‘is_active’ and the SELECT id, first_day for the status table. How embarrassing!

Could anybody please explain, when we calculating Active users for multiple month
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
FROM cross_join)

Why does in this case they have given as “subscription_end > first_day” ?
should’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’ ?

This answer is the solution to the above problem. I hope it helps, very few explanations here…

3 Likes

I’m super annoyed with this section… I have understood it all except this whole CHURN section.

1 Like

Hello, I’ve been trying to fiddle with this statement and cannot seem to get it to work, can anyone explain where its wrong?

Capture d’écran 2020-09-23 164625

I don’t understand something.
When i rename the first day collum by month and i’m using “month” in my case statement to make my conditions.
When subscription_start < ‘month’ AND… It doesn’t work ! i have understood that i should change ‘month’ by ‘first_day’ to make it work but i don’t understand why.

Thank you so much in advance for your answers !

Hey, I struggled on this a whole lot, and ended up finally just looking up the solution so I could compare what I was doing to what I should be doing. Then another 20 minutes of staring at the two to see the difference. You have the same issue I had,
plus one more.

I still don’t quite see what takes priority when, but it instead of using ‘month’ it is still first_day, because your first_day as ‘month’ only renames that column, not the code,
and secondly, which I don’t get, is the parenthesis. It should look like this
… 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)

Notice how the subscription_start has its own brackets, and everything doing with subscription_end is in it’s own.

I don’t quite get it

Morning hive mind,

So I ended up looking at the solution sadly… and realize that my mistake was that :

END AS ‘is_active’

instead of

END AS is_active

I can’t find any answer on internet but I would like to understand ,
so fare we pretty much need to put the rename AS with apostrophe or quotation mark
why for a case statement the quotation mark or apostrophe create an error ?
this is really confusing…

It helped! I was missing one peace of the puzzle. Thanks!

It would really help me to be able to see the tables as I make them. It is hard for me to hold these tables in my head with column names.