I am working on the bonus question for the user churn project in SQL
The question is:
How would you modify this code to support a large number of segments?
I have watched the video and have successfully created a query with simpler code, but my churn rates are completely different and wrong than when I do the original, more clunky query.
Can someone please check my code to see where I’m going wrong?
I’ve uploaded a screen shot for what the correct answers should be for churn rate for both segment 87 and 30 (the only two segments in the data set).
My query is visible here, as well as below
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, segment, CASE WHEN (subscription_start > first_day) AND (subscription_end < last_day OR subscription_end IS NULL) THEN 1 ELSE 0 END AS is_active, CASE WHEN (subscription_end BETWEEN first_day AND last_day) THEN 1 ELSE 0 END AS is_canceled FROM cross_join ), status_aggregate AS ( SELECT month, segment, SUM(is_active) AS sum_active, SUM(is_canceled) AS sum_canceled FROM status GROUP BY month, segment ), churn_rate AS( SELECT month, segment, ROUND((1.0* sum_canceled / sum_active),2) AS churn_rate FROM status_aggregate GROUP BY month, segment) SELECT * FROM churn_rate; ```![Screen Shot 2020-05-28 at 2.03.34 PM|690x151](upload://RcRSm9LenL5fHlHiFEXpP1KtxS.png)