Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created May 16, 2020 01:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save codecademydev/9aabe27f9ff7c951f2879a2471b396a4 to your computer and use it in GitHub Desktop.
Save codecademydev/9aabe27f9ff7c951f2879a2471b396a4 to your computer and use it in GitHub Desktop.
Codecademy export
SELECT * FROM subscriptions
LIMIT 100;
SELECT MIN(subscription_start), MAX(subscription_end)
FROM subscriptions;
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',
CASE
WHEN (segment = 87)
AND (subscription_start < first_day)
THEN 1
ELSE 0
END AS 'is_active_87',
CASE
WHEN (segment = 30)
AND (subscription_start < first_day)
THEN 1
ELSE 0
END AS 'is_active_30',
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),
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'
FROM status GROUP BY month)
SELECT
1.0 * sum_canceled_87 / sum_active_87
AS 'churn_rate_87',
1.0 * sum_canceled_30 / sum_active_30
AS 'churn_rate_30'
FROM status_aggregate;
@ElenaKosourova
Copy link

Слайд1
Слайд2
Слайд3
Слайд4

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment