historical_churn_exclude_countdown

Purpose: This view is the same as historical_churn, except it excludes countdownTimer (and all its aliases) 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 historical_churn_exclude_countdown AS
WITH subs AS (
  SELECT
    user_id,
    created_at,
    downgraded_at,
    BOOL_OR(active) OVER(PARTITION BY 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
      WHERE NOT EXISTS (SELECT 1 FROM app_details ad JOIN apps a ON ad.app_type = a.app_type AND a.id = ass.app_id WHERE (ad.app_type = 'countdownTimer' OR ad.alias = 'countdownTimer'))
    UNION
    SELECT
      ps.user_id,
      ps.created_at,
      ps.downgraded_at,
      ps.active
    FROM pro_subscriptions ps
    WHERE NOT EXISTS (SELECT 1 FROM app_details ad WHERE ad.app_type = ps.app_type AND (ad.app_type = 'countdownTimer' OR ad.alias = 'countdownTimer'))) 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;