Codecademy export

test.sqlite

-- -- -- 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;