Codecademy Forums

My sql code is not giving any result

https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/projects/calc-churn-proj

in this exercice i am not getting any result when i am attempting fifth question.

Hello :slight_smile: 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 :slight_smile:

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 :wink:

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! :slight_smile:

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.:slightly_smiling_face:

1 Like

You’re very welcome :slight_smile:

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 :slight_smile: 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 :slight_smile:

Eh, there should be error messages in this course :frowning:

1 Like

Thank you so much! :grin:

1 Like

You’re very welcome :slight_smile:

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