So I’m working on the Churn Rate exercise here, and I’m not quite sure where I went wrong because nothing shows up when I save it. I feel like I’m missing something obvious, but I’ve been messing with it for about an hour and can’t figure it out on my own.
Here is what I have right now:
WITH months AS (SELECT '2016-01-01' as 'first_day', '2016-01-31' as 'last_day' UNION SELECT '2016-02-01' as 'first_day', '2016-02-28' as 'last_day' UNION SELECT '2016-03-01' as 'first_day', '2016-03-31' as 'last_day') WITH cross_join AS (SELECT subscriptions.*, months.* FROM subscriptions CROSS JOIN months) WITH status AS (SELECT cross_join, first_day AS 'month', CASE WHEN (segment = '87' AND subscription_start < first_day) THEN 1 ELSE 0 END AS is_active_87, CASE WHEN (segment = '30' AND subscription_start < first_day) THEN 1 ELSE 0 END AS is_active_30, CASE WHEN (segment = '87' AND subscription_end IS BETWEEN first_day AND last_day) THEN 1 ELSE 0 END AS is_canceled_87, CASE WHEN (segment = '30' AND subscription_end IS BETWEEN first_day AND last_day) THEN 1 ELSE 0 END AS is_canceled_30 FROM cross_join) WITH 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 month, 1.0 * (sum_canceled_87 / sum_active_87), 1.0 * (sum_canceled_30 / sum_canceled_30) FROM status_aggregate GROUP BY month;
Any help would be most appreciated. Thanks in advance!