Hi there,
I was checking if the result for Task 8 from the exercise ‘Calculating Churn Rates’ had the same result with the provided Unstuck video, and it turned out that I had came up with a different result than in the video. If you could be so kind and have a look at it, and could tell me why my results are different I’d be delighted.
So the result I found with churn rates was;
churn_rate_87 | churn_rate_30
3.971 | 13.227
3.121 | 13.631
2.058 | 8.523
While the get unstuck video came with results;
0.251 | 0.075
0.320 | 0.073
0.485 | 1.117
Here is the code I ended with;
WITH months AS (
SELECT
‘2016-12-01’ AS first_day,
‘2016-12-31’ AS last_day
UNION
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 month,
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_active_87/sum_canceled_87 AS churn_rate_87,
1.0 * sum_active_30/sum_canceled_30 AS churn_rate_30
FROM status_aggregate;
And here is the link where you can see the result the Get Unstuck video has;
I suggest to jump to 41:45 for the final result Brendan suggests.
I am wondering if anybody will answer to this.
Cheers.