--#1 SELECT * FROM subscriptions LIMIT 100; SELECT segment, COUNT(DISTINCT id) AS 'users' FROM subscriptions GROUP BY 1; --#2 SELECT MIN(subscription_start), MIN(subscription_end), MAX(subscription_end) FROM subscriptions; --#3 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' FROM subscriptions ) --#4 cross_join AS (SELECT * FROM subscriptions CROSS JOIN months ) --#5 status AS (SELECT id, first_day AS month, CASE WHEN segment = 87 AND subscription_start < first_day AND (subscription_end > first_day OR subscription_end IS NULL) THEN 1 ELSE 0 END AS is_active_87, CASE WHEN segment = 30 AND subscription_start < first_day AND (subscription_end > first_day OR subscription_end IS NULL) THEN 1 ELSE 0 END AS is_active_30 FROM cross_join ) --#6 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 ) --#7 status_aggregate AS (SELECT month, SUM(is_active_87) AS active_87, SUM(is_active_30) AS active_30, SUM(is_canceled_87) AS cancel_87, SUM(is_canceled_30) AS cancel_30 FROM status GROUP BY 1 ) --#8 SELECT month, ROUND(1.0 * cancel_87 / active_87,2) AS churn_rate_87, ROUND(1.0 * cancel_30 / active_30,2) AS churn_rate_30 FROM status_aggregate; --#9 SELECT month, segment, ROUND(1.0 * canceled / active,2) AS churn_rate FROM status_aggregate GROUP BY 1, 2 ORDER BY 2;