You must select a tag to post in this category. Please find the tag relating to the section of the course you are on E.g. loops, learn-compatibility
When you ask a question, don’t forget to include a link to the exercise or project you’re dealing with!
If you want to have the best chances of getting a useful answer quickly, make sure you follow our guidelines about how to ask a good question. That way you’ll be helping everyone – helping people to answer your question and helping others who are stuck to find the question and answer!
I feel myself slowly going insane trying to understand why this isn’t running. It’s not throwing any errors on the RHS panel to guide me so kind of fumbling in the dark at the moment!
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 (segment = 87) AND (subscription_end BETWEEN first_day AND last_day) THEN 1 ELSE 0 END AS is_canceled_87, CASE WHEN (segment = 30) AND (subscription_end BETWEEN first_day AND last_day) 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(sum_canceled_87) AS sum_canceled_87, SUM(is_canceled_87) AS sum_canceled_30 FROM status GROUP BY month, segment ) SELECT month, 1.0 * sum_canceled_87 / sum_active_87 AS churn_rate, 1.0 * sum_canceled_30 / sum_active_30 AS churn_rate FROM status_aggregate;