user_churn.sql

SELECT * FROM subscriptions LIMIT 5;

--Determine the range of data
--Data starts in 12-2016; however, there will be no 
--subscription_end values in the first month
SELECT MIN(subscription_start), MAX(subscription_start)
FROM subscriptions;

--This table does not have months prebuilt into it
--We can create a temp one using
WITH months AS (
  SELECT
  '2017-01-01' AS first_day, --Jan
  '2017-01-31' AS last_day
  UNION 
  SELECT 
    '2017-02-01' AS first_day, --Feb
    '2017-02-28' AS last_day 
  UNION 
  SELECT 
    '2017-03-01' AS first_day, --March
    '2017-03-31' AS last_day
),

--Cross Join months table on subscriptions
cross_join AS (
  SELECT * 
  FROM subscriptions
  CROSS JOIN months
),
status AS ( -- Status table to determine if a user is an active subscriber or not
  SELECT id, first_day AS month,
  CASE -- 1 if user is segment 87 and active else 0
    WHEN (segment = 87)
      AND (subscription_start < first_day)
      AND (subscription_end IS NULL OR subscription_end >= first_day)
    THEN 1 ELSE 0
    END AS is_active_87,
  CASE -- 1 if user is segment 30 and active else 0
    WHEN (segment = 30)
      AND (subscription_start < first_day)
      AND (subscription_end IS NULL OR subscription_end >= first_day)
    THEN 1 ELSE 0
    END AS is_active_30,
  CASE -- 1 if user is segment 87 and NOT active else 0
    WHEN (segment = 87)
      AND (subscription_end BETWEEN first_day AND last_day)
    THEN 1 ELSE 0
    END AS is_canceled_87,
  CASE -- 1 if user is segment 30 and NOT active else 0
    WHEN (segment = 30) 
      AND (subscription_end BETWEEN first_day AND last_day)
    THEN 1 ELSE 0
    END AS is_canceled_30
    FROM cross_join
),
--aggregates to get the sum of active and canceled subscriptions
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 --returns churn rate as a % for segment 87 and 30
  month,
  ROUND((CASE WHEN sum_active_87 = 0 THEN NULL ELSE (sum_canceled_87 * 1.0 / sum_active_87) * 100 END), 3) AS churn_rate_87,
  ROUND((CASE WHEN sum_active_30 = 0 THEN NULL ELSE (sum_canceled_30 * 1.0 / sum_active_30) * 100 END), 3) AS churn_rate_30
FROM status_aggregate;