Hey guys, currently working on the “analyze data with SQL” skill path. I’ve had very little trouble so far but I’ve gotten stuck on this one project “calculating churn rates”. Got this piece of code with several temporary tables and a bunch of cases within one of them. The problem is that I can’t get anything to show up in the query results, regardless of what I’m trying to get to show up. Check out 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
)
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,
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
)
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
GROUP BY month
)
SELECT *
FROM status_aggregate;
Even when trying to only get all the info from the first ‘months’ table I can’t get anything to show up. I’m using the following query for it:
SELECT *
FROM months;
Can anyone spot any issues in my code? I’ve been looking all over this forum but even using code from other posts about this project I can’t get it to work?