churn_plus

Purpose: This View calculates churn as defined by people who have made at least 2 transactions if they have a monthly subscription (yearly and biennially automatically included). Fields:

date: day of the year
num_active: number of active paying users as of that day
num_cancelled: number of users who are no longer active as of that day
active_paying_users_30_days_ago: num_active 30 days ago…used for churn calculation
cancellations_last_30: SUM(num_cancelled) over the last 30 days…used for churn calculation

Notes:

• Only ever show for the current year
• Automatically updates yearly now 🙌
• Used only on Company Goals currently
• Only exists in SQL land, not in the Rails Schema currently

---

CREATE VIEW churn_plus AS
WITH subs AS (
  SELECT
s.user_id,
s.created_at,
s.downgraded_at,
BOOL_OR(s.active) OVER(PARTITION BY s.user_id) AS has_any_active_subscription
  FROM
(SELECT
  cc.user_id,
  ass.created_at,
  ass.downgraded_at,
  ass.active
FROM
  app_subscriptions ass
  INNER JOIN credit_cards cc ON cc.id = ass.credit_card_id
  JOIN LATERAL (
    SELECT
      t.app_subscription_id,
      CASE WHEN ass.subscription_type = 'monthly' THEN COUNT(t.app_subscription_id) > 1 ELSE true END AS flag
    FROM
      transactions t
    WHERE
      t.app_subscription_id = ass.id
    GROUP BY t.app_subscription_id
  ) t ON true
WHERE
  t.flag = true
UNION
SELECT
  ps.user_id,
  ps.created_at,
  ps.downgraded_at,
  ps.active
FROM pro_subscriptions ps
JOIN LATERAL (
  SELECT
    t.pro_subscription_id,
    CASE WHEN ps.subscription_type = 'monthly' THEN COUNT(t.pro_subscription_id) > 1 ELSE true END AS flag
  FROM
    transactions t
  WHERE
    t.pro_subscription_id = ps.id
  GROUP BY t.pro_subscription_id
) t ON true
WHERE
  t.flag = true) AS s
), date AS (
  SELECT GENERATE_SERIES(('12-01-' || (SELECT EXTRACT(YEAR FROM CURRENT_DATE) - 1))::DATE, NOW()::DATE, '1 day') AS date
), active_users AS (
  SELECT
date.date,
COUNT(DISTINCT user_id) AS num_active
  FROM subs
INNER JOIN date ON subs.created_at < date.date AND (subs.downgraded_at IS NULL OR subs.downgraded_at > date.date)
  GROUP BY date.date
), cancelled_users AS (
  SELECT
date.date,
count(distinct user_id) AS num_cancelled
  FROM
subs
INNER JOIN date ON subs.downgraded_at::DATE = date.date AND subs.has_any_active_subscription = false
  AND NOT EXISTS ( SELECT 1 FROM subs s2 WHERE s2.user_id = subs.user_id AND s2.downgraded_at::DATE > date.date)
  GROUP BY date.date
)
SELECT
  au.date::DATE,
  au.num_active,
  cu.num_cancelled,
  SUM(au.num_active) OVER(ORDER BY au.date ROWS BETWEEN 30 PRECEDING AND 30 PRECEDING) AS active_paying_users_30_days_ago,
  SUM(cu.num_cancelled) OVER(ORDER BY au.date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS cancellations_last_30
FROM
  active_users au
  JOIN cancelled_users cu ON cu.date = au.date;