User Churn Code not working

I have written the following code for the Codeflix User Churn project, and while it doesn’t generate any errors I am also not seeing any results for the query, can someone help me understand what am I doing wrong?
WITH months AS
(
SELECT ‘2016-12-01’ as first_day,
‘2016-12-31’ as last_day
UNION
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 months.,subscriptions.
FROM months
CROSS JOIN subscriptions
),
status AS
(
SELECT id, first_day AS month,
CASE
WHEN ((subscription_start<first_day)
AND (segment = 87)
AND (subscription_end>=last_day)
OR (subscription_end IS NULL))
THEN 1
ELSE 0
END as is_active_37
CASE
WHEN ((subscription_start<first_day)
AND (segment = 37)
AND (subscription_end>=last_day)
OR (subscription_end IS NULL))
THEN 1
ELSE 0
END as is_active_37
CASE
WHEN
((subscription_end BETWEEN first_day AND last_day) AND (segment = 87))
THEN 1
ELSE 0
END as is_cancelled_87
CASE
WHEN
((subscription_end BETWEEN first_day AND last_day) AND (segment = 37))
THEN 1
ELSE 0
END as is_cancelled_37
),
status_aggregate AS
(
SELECT
SUM(is_active_87) AS sum_active_87,
SUM(is_active_37) AS sum_active_37,
SUM(is_canceled_87) AS sum_canceled_87,
SUM(is_canceled_37) AS sum_canceled_37,
FROM status
)

SELECT 1.0 * (sum_canceled_87/sum_active_87) AS 87_churn,
1.0 * (sum_canceled_37/sum_active_37) AS 37_churn
FROM status_aggregate;

1 Like

Hi @babylongbot ,

You have two “END AS is is active 37”
In your CASE statements above.

1 Like

Hi, thanks for pointing that out.
I have made the change but it is still not working

There is also a small typo above. There’s an inadvertent comma after the period in months.subsriptions

1 Like

This is Q3, right?
" You’ll be calculating the churn rate for both segments ( 87 and 30 ) over the first 3 months of 2017 (you can’t calculate it for December, since there are no subscription_end values yet). To get started, create a temporary table of months ."

Double check the dates in your WITH months CTE.

1 Like

Thanks so much for all your help and insight I have made the edits as you have pointed out but unfortunately it is still not executing, I am pasting the code here again, as maybe the updated code may be of better use.
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 months
CROSS JOIN subscriptions
),

status AS
(
SELECT id, first_day AS month,
CASE
WHEN ((subscription_start<first_day)
AND (segment = 87)
AND (subscription_end>=last_day)
OR (subscription_end IS NULL))
THEN 1
ELSE 0
END as is_active_87
CASE
WHEN ((subscription_start<first_day)
AND (segment = 30)
AND (subscription_end>=last_day)
OR (subscription_end IS NULL))
THEN 1
ELSE 0
END as is_active_30
CASE
WHEN
((subscription_end BETWEEN first_day AND last_day) AND (segment = 87))
THEN 1
ELSE 0
END as is_cancelled_87
CASE
WHEN
((subscription_end BETWEEN first_day AND last_day) AND (segment = 30))
THEN 1
ELSE 0
END as is_cancelled_30
),

status_aggregate AS
(
SELECT
SUM(is_active_87) AS sum_active_87,
SUM(is_active_30) AS sum_active_30,
SUM(is_canceled_87) AS sum_canceled_87,
SUM(is_canceled_30) AS sum_canceled_30,
FROM status
)

SELECT 1.0 * (sum_canceled_87/sum_active_87),
1.0 * (sum_canceled_30/sum_active_30)
FROM status_aggregate;

Sure! no problem! :slight_smile:

I also read/check my code from the bottom up to catch any errors. Sometimes that helps. Personal preference, I suppose.
Let’s see…

Try swapping the tables in this part. (the FROM and CROSS JOIN part.

The Q asks: ’ Create a temporary table, cross_join , from subscriptions and your months . Be sure to SELECT every column."

as before, Thanks for this tip too, I tried it yet the code doesn’t seem to be working for some reason, I tried running it in my Mac’s terminal and it points out an error near the ‘CASE’ syntax. Anyway I am going to try to write the code again from the beginning hopefully, clearing any errors, thanks again for your help. The coding community is great :slight_smile:

1 Like

I give up !
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 subscriptions.,months.
FROM subscriptions
CROSS JOIN months
),
status AS
(
SELECT id,
first_day AS month,
CASE
WHEN
(
(segment = 87)
AND
(subscription_start < first_day)
AND
(subscription_end >= last_day)
OR
(subscription_end IS NULL)
)
THEN 1
ELSE 0
END AS is_active_87
CASE
WHEN
(
(segment = 30)
AND
(subscription_start < first_day)
AND
(subscription_end >= last_day)
OR
(subscription_end IS NULL)
)
THEN 1
ELSE 0
END AS is_active_30
CASE
WHEN
(
(segment = 87)
AND
(subscription_start < first_day)
AND
(WHERE subscription_end BETWEEN first_day AND last_day)
THEN 1
ELSE 0
END AS is_canceled_87
)
CASE
WHEN
(
(segment = 30)
AND
(subscription_start < first_day)
AND
(WHERE subscription_end BETWEEN first_day AND last_day)
THEN 1
ELSE 0
END AS is_canceled_30
)
),
status_aggregate AS
(
SELECT
SUM(is_active_87) AS sum_active_87,
SUM(is_active_37) AS sum_active_37,
SUM(is_canceled_87) AS sum_canceled_87,
SUM(is_canceled_30) AS sum_canceled_30
)

SELECT
1.0 * (sum_canceled_87/sum_active_87) AS churn87,
1.0 * (sum_canceled_30/sum_active_87) AS churn30
FROM status_aggregate;

Still not working…

The Q says to ‘select every column’. You also have a stray comma there.

Did you happen to watch the help video? I think this is the one I went through recently b/c all my queries weren’t showing results. I erased/reset my code, followed the video and did each step (and saw the results in the output window), and it worked that way, but if I put all the queries in the terminal window (in the lesson) it didn’t work. By that, I mean, all the results didn’t appear. The video also says “deprecated SQL Project” So, I wonder if it won’t work? Maybe this is a bug?

I knew I had encountered this before.
Here’s the thread:
https://discuss.codecademy.com/t/churn-rate-part-5-please-help/514690

I edited out the comma, and I have experienced the same thing, the snippets of code work individually but when it’s all put together for some reason the queries are not returned.
ultimately It’s okay as my goal was to get the concept which, after writing the code 3 times, I am sure I do hahaha regardless, thanks for all your help @lisalisaj
p.s. Ill see the thread you’ve posted below.

1 Like

Sure, anytime! :slight_smile:

Are you able to complete the lesson? do you get a badge for it?

Yes, I have completed the lesson and the one after it too.
I received badges for both of them and am now about to start Python.
If possible I would love to remain connected with you through a platform such as Github, etc. as I am a newbie to coding and don’t really know a lot about the same :grimacing:
Although, I understand if you are not comfortable sharing your id

Good to know!

I submitted a Bug Report so, I’ll see if anything gets resolved with the lesson/project.

I’m usually around here on the forums. If you ever have issues, you can always search for a topic or post here and I or anyone else can try to help out. Lots of helpful learners here. :slight_smile:
And, if you can ever help anyone else out, that’s great too b/c that’s what this whole journey & community is about. :cowboy_hat_face:

1 Like

Sure, I’ll try to help all the coders I can if I ever come across a problem that could use my input