MRR by customer month

1-- core.chargebee.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 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.chargebee.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 cancelled immediately stops on
16-- the day of its cancellation credit note.
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_chargebee__mrr_movements_reconcile.sql checks it.
26--
27-- Movements are measured per CUSTOMER, which is how Chargebee and most SaaS
28-- reporting count them: a customer moving from plan A to plan B is an expansion
29-- or a contraction, not a churn plus a new. Weld does not sync the subscription id
30-- on invoice lines, so a subscription grain is not reliable for consolidated
31-- invoices; `subscription_id` is still on recurring_revenue_lines where known.
32--
33-- The snapshot uses invoices. 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.chargebee.recurring_revenue_lines}}
39),
40