Analyze Data with SQL - Calculating Churn Rates - Q5 Query Check

Hello,

I have been trying to analyze why my Query for Question 5 on the Analyze Data with SQL - Calculating Churn Rates course is not returning results. I have proof read it and checked against the walk-through video, trying to match what I have to what it’s showing on the video, however I have yet to get any results.

Any help is greatly appreciated, appreciate your time! :slight_smile:

Link to course: 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

Walkthrough video: [DEPRECATED] SQL Project Calculating Churn Rates - YouTube

Query Below:

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
),
(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 10;

Hello, and welcome to the forums!

If you break down your query, you’re creating a few temporary views and using SELECT to get data from them as you continue. However, you didn’t name one of them, so the query errors out and the Codecademy learning environment doesn’t display anything.

Creating temporary views using:

months AS ( ... )
cross_join AS ( ... )

Selecting from them:

SELECT ... FROM months
SELECT ... FROM cross_join
SELECT ... FROM status

Notice that you’re trying to retrieve data from one named status, but you didn’t name that one.

Click if you need more help

This is the one you didn’t name.

(SELECT id, first_day AS month, 

should be

status AS (SELECT id, first_day AS month,
4 Likes

Thanks a lot! I managed to get it to run successfully today, appreciate your help!

Best Wishes,
Stefan