in this exercice i am not getting any result when i am attempting fifth question.
Hello 
Welcome to the forum.
Please post your code. We simply cannot help if we don’t know what is the problem.
In this article -> How to ask good questions (and get good answers) you will find some helpful tips on how to help us to help you
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’
FROM subscriptions
),
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’
FROM cross_join
)
SELECT *
FROM status
LIMIT 10;
The article I linked above also talks about code formatting 
Take a look at these two lines:
SELECT ‘2017-01-01’ AS ‘first-day’,
WHEN (subscription_start < first_day)
Do you see some inconsistency in the naming? Awesome, you can fix this!
By the way, name first_day is a much better choice. first-day looks like an expression, you would have to use brackets in comparisons:
WHEN (subscription_start < [first-day])
Use first_day and last_day.
It is not working even after I changed “first-day” as “first_day”
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
FROM subscriptions
),
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
FROM cross_join
)
SELECT *
FROM status
LIMIT 10;
Good job!
There is only one slight problem left, here:
)
status AS
status is the next temporary table, so you need to add ,:
),
status AS
Thank you for taking time and helping me out.
1 Like
You’re very welcome
Hi,
I’ve had the same issue with this question on the project, even after using the above (corrected) code in my project it has still returned no results. Went through the walkthrough video over and over and still nothing - the code seems to work up to question 4 but it stops returning results after this.
Here is the code I’ve managed to do:
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) 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
END AS 'is_active_30'
FROM cross_join)
SELECT *
FROM status
LIMIT 50;
Hello Welcome to the forum!
Two times in your code you are referring to the first_day column as first day. This is why the query does not work. Simply a typo
Eh, there should be error messages in this course 
1 Like
Thank you so much! 
1 Like
You’re very welcome
1 Like
Hi Guys,
Also on this challenge and can’t seem to see my mistake :
status 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
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_canceled_87,
CASE
WHEN
(subscription_end BETWEEN first_day AND last_day)
AND
(segment = 30) THEN 1
ELSE 0
END AS is_canceled_30
FROM cross_join
)
please post your full code
1 Like
I’m having the same result. Can’t see any error in my code!
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
FROM subscriptions),
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)
AND (segment = 87) THEN 1
ELSE 0
END AS is_active_87,
CASE
WHEN (subscription_start < first_day)
AND (subscrption_end > first_day
OR subscription_end IS NULL)
AND (segment = 30) 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
FROM cross_join)
SELECT * FROM status LIMIT 10;
I found inconsistency here. Change subscrption to subscription.
1 Like
I’m not getting a response and I can’t see where I’m making my mistake. Code below. Help greatly appreciated.
WITH months AS
(SELECT
‘2017-01-01’ AS first_day,
‘2017-01-31’ AS last_day
UNION
‘2017-02-01’ AS first_day,
‘2017-02-28’ AS last_day
UNION
‘2017-03-01’ AS first_day,
‘2017-03-30’ 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_start > 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_start > first_day)
OR (subscription_end IS NULL)
AND (segment = 30)
THEN 1
ELSE 0
END AS is_active_30,
CASE
WHEN subscription_end BETWEEN
first_day AND last_day
AND (segment = 87)
OR subscription_end IS NOT NULL
THEN 1
ELSE 0
END AS is_canceled_87,
CASE
WHEN subscription_end BETWEEN
first_day AND last_day
AND (segment = 30)
OR subscription_end IS NOT NULL
THEN 1
ELSE 0
END AS is_canceled_30
FROM cross_join)
SELECT *
FROM status
LIMIT 10;
1 Like
UPDATE: I figured it out. In the first temporary table, I wasn’t putting a ‘SELECT’ statement in for each month date range (a dumb mistake) but something that took me an hour and then some coming back to see. In the meantime, I was able to refactor some code and got all the answers correct. WOW. What a learning experience. Here is my final code:
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-30’ 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_start > 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_start > first_day
OR subscription_end IS NULL)
AND (segment = 30)
THEN 1
ELSE 0
END AS is_active_30,
CASE
WHEN (subscription_end BETWEEN
first_day AND last_day)
OR (subscription_end IS NOT NULL)
AND (segment = 87)
THEN 1
ELSE 0
END AS is_canceled_87,
CASE
WHEN (subscription_end BETWEEN
first_day AND last_day)
OR (subscription_end IS NOT NULL)
AND (segment = 30)
THEN 1
ELSE 0
END AS is_canceled_30
FROM cross_join),
status_aggregate AS
(SELECT
month,
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
GROUP BY month)
SELECT
month,
ROUND((1.0 * (sum_canceled_87) / (sum_active_87)),2) AS ‘Churn_87’,
ROUND((1.0 * (sum_canceled_30) / (sum_active_30)),2) AS ‘Churn_30’
FROM status_aggregate
GROUP BY month;
1 Like