Hello~
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!