Codecademy export

test.sqlite

WITH months AS
(SELECT
  '2017-01-01' AS first_date,
  '2017-01-31' AS last_date
  UNION
  SELECT
  '2017-02-01' AS first_date,
  '2017-02-28' AS last_date
  UNION
  SELECT
  '2017-03-01' AS first_date,
  '2017-03-31' AS last_date),

cross_join AS 
  (SELECT *
  FROM subscriptions
  CROSS JOIN months),

status AS
(SELECT id, first_date AS month,
CASE
  WHEN (segment = 87 )
  AND (subscription_start < first_date)
  AND (subscription_end > first_date
    OR subscription_end is NULL)
  THEN 1
  ELSE 0
  END AS is_active_87,
CASE
  WHEN (segment = 30)
  AND (subscription_start < first_date)
  AND (subscription_end > first_date 
    OR subscription_end is NULL)
  THEN 1
  ELSE 0
  END AS is_active_30,
  CASE
    WHEN (segment = 87 )
    AND subscription_end
    BETWEEN first_date AND last_date
    THEN 1 ELSE 0
    END AS is_canceled_87,
  CASE
    WHEN (segment = 30 )
    AND subscription_end
    BETWEEN first_date AND last_date
    THEN 1 ELSE 0
    END AS is_canceled_30
  FROM cross_join
),
status_aggregate AS 
(
  SELECT month, 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 month,
1.0 * sum_canceled_87 / sum_active_87 AS churn_87,
1.0 * sum_canceled_30 / sum_active_30 AS churn_30
FROM status_aggregate;