User Churn Codeflix Calculating Churn Rate

[jamesstage2436766563]

Hello everyone!

Thank you for taking time to look at this! I am having trouble with my user churn project https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/projects/calc-churn-proj.

I’m stuck on step 5 and my code won’t return a table. I am sure I am missing something obvious but I cannot tell what. Any suggestions would be greatly appreciated. The video shows that you can return results here to check on the table but mine returns nothing and doesn’t indicate where the code is going wrong.

Thanks again!

with months as
(select
‘2017-1-01’ as first_day,
‘2017-1-31’ as last_day
union
select
‘2017-2-01’ as first_day,
‘2017-2-28’ as last_day
union
select
‘2017-3-01’ as first_day,
‘2017-3-30’ as last_day
),
cross_join as
(select *
from subscriptions
cross join months
),
staus as
(select
id,
first_day as month,
case
when (subscription_start < first_day) and (subscription_end > first_day or subscription_end is null) and (segment = 87) then 1
else 0
end as is_active_87,
case
when (subscription_start < first_day) and (subscription_end > first_day or subscription_end is null) and (segment = 30) then 1
else 0
from cross_join
)
select *
from status
limit 10;

[jamesstage2436766563]

I realized that i didnt have an END AS statement at the end of the code. I have added that after the ELSE statement in the second case but it still doens’t work.

[toastedpitabread]

Try to write down on paper what each table you create needs. I think you’re missing some elements for your status table which is why it’s not getting the results you want.

It’s also good to see what you select before you start writing any code (so commenting out the case statements can clarify for you what you’re filtering here).

Let me know if that helps.

[jamesstage2436766563]

Thank you for the advice, rewriting it helped. I misspelled the “status as” statement and didnt notice until I ran through it from scratch!