MRR by customer month

1-- core.recurly.mrr_by_customer_month
2-- MRR per customer per month, and the movement that took it there: new,
3-- expansion, contraction, churn or reactivation.
4--
5-- Grain: month x customer (Recurly account) x currency. A customer appears from the first month they
6-- had MRR, in every month they had MRR, and in the month they churned. Months
7-- after a churn with no MRR are left out until the customer comes back.
8-- Currency: the invoice currency, major units. A customer billed in two
9-- currencies has two rows per month.
10-- Depends on: core.recurly.recurring_revenue_lines
11--
12-- MRR AT A MONTH is the sum of monthly_amount over every recurring line whose
13-- service period covers the snapshot date: the last day of the month, or today
14-- for the month in progress. A subscription cancelled at the end of its term stops
15-- counting the day its last paid period ends; one terminated with a prorated or
16-- full refund stops on the day the refund credit starts.
17--
18-- MOVEMENTS, with beginning_mrr = the previous month's MRR:
19--   new           beginning 0, MRR > 0, and never had MRR before
20--   reactivation  beginning 0, MRR > 0, had MRR in some earlier month
21--   expansion     beginning > 0, MRR higher
22--   contraction   beginning > 0, MRR lower but above 0      (stored negative)
23--   churn         beginning > 0, MRR 0                      (stored negative)
24-- So beginning_mrr + new + expansion + contraction + churn + reactivation =
25-- mrr, on every row. tests/assert_recurly__mrr_movements_reconcile.sql checks it.
26--
27-- Movements are measured per CUSTOMER, which is how most SaaS reporting counts
28-- them: a customer moving from plan A to plan B, or replacing one subscription
29-- with another, is an expansion or a contraction, not a churn plus a new. For a
30-- per-subscription view, group recurring_revenue_lines by `subscription_id`,
31-- which Recurly puts on every line item.
32--
33-- The snapshot uses invoiced line items. A renewal that has not been invoiced yet, or usage
34-- billed in arrears, is missing until the invoice exists, so the current month
35-- is provisional until month end.
36
37WITH lines AS (
38    SELECT * FROM {{core.recurly.recurring_revenue_lines}}
39),
40