Subscription churn

1-- core.recurly.subscription_churn
2-- Every subscription that has ended, why it ended, and how much MRR it was
3-- carrying just before. Splits churn into customers who chose to leave and
4-- customers lost to failed payments, which the MRR bridge cannot tell apart.
5--
6-- Grain: one row per expired subscription. A subscription that is canceled but
7-- still running until the end of its term is not here yet; it arrives the day it
8-- expires. Currency: the subscription currency, major units.
9-- Depends on: staging.recurly.subscription, staging.recurly.account,
10-- core.recurly.recurring_revenue_lines
11--
12-- CHURN TYPE, from Recurly's expiration_reason:
13--   voluntary            canceled: the customer or an admin ended it
14--   involuntary          nonpayment (dunning gave up) or tax_location_invalid
15--                        (tax validation failed and the account was never fixed)
16--   term_ended           fixed billing cycles ran out without renewal
17--   trial_not_converted  a cardless trial, or a gift, ended with no billing info
18--   other                any reason not listed above, kept as is in
19--                        expiration_reason so it can be mapped
20-- is_account_closed is separate: a closed account expires its subscriptions as
21-- canceled, so it is a flag on top of the type rather than a type of its own.
22--
23-- MRR AT EXPIRY is the subscription's MRR on the day before it expired, from the
24-- same line items and normalisation as the MRR models, floored at 0. It is 0 for
25-- a trial that never converted, and 0 when a termination refunded the last
26-- period in full, because the refund credit takes that period back out.
27--
28-- This model counts subscriptions. The MRR bridge counts customers, so an
29-- account that swaps one subscription for another is churn here and an expansion
30-- or contraction there. Compare the two by customer, not by row count.
31
32WITH ended AS (
33    SELECT
34        s.*,
35        DATE(s.expires_at) AS expired_on
36    FROM {{staging.recurly.subscription}} s
37    WHERE s.state = 'expired'
38      AND s.expires_at IS NOT NULL
39),
40