Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created Oct 21, 2020
Embed
What would you like to do?
Codecademy export
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 >= first_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 segment, month,
SUM(is_active) AS sum_active,
SUM(is_canceled) AS sum_canceled
FROM status
GROUP BY segment, month)
SELECT segment,
strftime('%m', month) AS Month,
ROUND(1.0 * sum_canceled / sum_active, 2) AS churn_rate
FROM status_aggregate;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment