Skip to content

Instantly share code, notes, and snippets.

@codecademydev codecademydev/test.sqlite Secret

Created May 26, 2020
Embed
What would you like to do?
Codecademy export
-- -- -- SELECT *
-- -- -- FROM subscriptions
-- -- -- LIMIT 100;
-- -- -- SELECT MIN(subscription_start)
-- -- -- FROM subscriptions;
-- -- -- SELECT MAX(subscription_start)
-- -- -- FROM subscriptions;
-- -- -- NOTE: range of dates = 2016-12-01 until 2017-03-30 ---> refers to the 2nd question.
-- -- -- NOTES TO SELF:
-- -- -- - Do not forget to type END AS when using a CASE/WHEN statement
-- -- -- START OF QUESTION 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'
),
cross_join AS
(SELECT *
FROM subscriptions
CROSS JOIN months
),
status AS
(SELECT cross_join.id, first_day AS 'month'
CASE
WHEN (subscription_start < first_day)
AND (segment = '87') THEN 1
ELSE 0
END AS 'is_active_87'
CASE
WHEN (subscription_start < first_day)
AND (segment = '30') THEN 1
ELSE 0
END AS 'is_active_30'
CASE
WHEN (subscription_end < last_day)
AND (segment = '87') THEN 1
ELSE 0
END AS 'is_canceled_87'
CASE
WHEN (subscription_end < last_day)
AND (segment = '30') THEN 1
ELSE 0
END AS 'is_canceled_30'
),
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'
GROUP BY month
),
SELECT
(
month,
1.0 * (SUM(sum_canceled_87) / SUM(sum_active_87)) AS 'churn_rate_87',
1.0 * (SUM(sum_canceled_30) / SUM(sum_active_30)) AS 'churn_rate_30'
)
FROM subscriptions
GROUP BY month;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.