churn.sql

WITH monthly_usage AS (
    SELECT
      user_id,
      date_part('month', age(created_at, '1970-01-01')) +
      12 * date_part('year', age(created_at, '1970-01-01')) AS time_period
    FROM orders
    WHERE order_state = 'completed'
    GROUP BY 1, 2
    ORDER BY 1, 2)

  ,

    lag_lead AS (
      SELECT
        user_id,
        time_period,
        lag(time_period, 1)
        OVER (PARTITION BY user_id
          ORDER BY user_id, time_period),
        lead(time_period, 1)
        OVER (PARTITION BY user_id
          ORDER BY user_id, time_period)
      FROM monthly_usage),

    lag_lead_with_diffs AS (
      SELECT
        user_id,
        time_period,
        lag,
        lead,
        time_period - lag  lag_size,
        lead - time_period lead_size
      FROM lag_lead),

    calculated AS (SELECT
                     time_period,
                     CASE WHEN lag IS NULL
                       THEN 'NEW'
                     WHEN lag_size = 1
                       THEN 'ACTIVE'
                     WHEN lag_size > 1
                       THEN 'RETURN'
                     END AS this_month_value,

                     CASE WHEN (lead_size > 1 OR lead_size IS NULL)
                       THEN 'CHURN'
                     ELSE NULL
                     END AS next_month_churn,

                     count(DISTINCT user_id)
                   FROM lag_lead_with_diffs
                   GROUP BY 1, 2, 3)

SELECT
  time_period,
  this_month_value,
  sum(count)
FROM calculated
GROUP BY 1, 2
UNION
SELECT
  time_period + 1,
  'CHURN',
  count
FROM calculated
WHERE next_month_churn IS NOT NULL
ORDER BY 1