MRR movements

1-- core.recurly.mrr_movements
2-- The MRR bridge by month: opening MRR, new, expansion, contraction, churn,
3-- reactivation, closing MRR, plus customer counts. The movements Recurly's
4-- subscription analytics show, rebuilt from invoiced line items.
5--
6-- Grain: month x currency. Currency: the invoice currency, major units. Do not
7-- add rows of different currencies together; convert first (see the README).
8-- Depends on: core.recurly.mrr_by_customer_month
9--
10-- All movement columns are sums of the customer-level columns, so this model
11-- reconciles exactly to mrr_by_customer_month. Churn and contraction are
12-- negative, so the bridge is plain addition:
13--   beginning_mrr + new + expansion + contraction + churned + reactivation = ending_mrr
14--
15-- Rates are computed from the summed amounts (ratio of sums), never averaged
16-- across customers.
17
18WITH by_month AS (
19    SELECT
20        month,
21        currency,
22        SUM(beginning_mrr)                          AS beginning_mrr,
23        SUM(new_mrr)                                AS new_mrr,
24        SUM(expansion_mrr)                          AS expansion_mrr,
25        SUM(contraction_mrr)                        AS contraction_mrr,
26        SUM(churned_mrr)                            AS churned_mrr,
27        SUM(reactivation_mrr)                       AS reactivation_mrr,
28        SUM(ending_mrr)                             AS ending_mrr,
29        COUNTIF(beginning_mrr > 0)                  AS beginning_customers,
30        COUNTIF(movement_type = 'new')              AS new_customers,
31        COUNTIF(movement_type = 'reactivation')     AS reactivated_customers,
32        COUNTIF(movement_type = 'churn')            AS churned_customers,
33        COUNTIF(movement_type = 'expansion')        AS expanded_customers,
34        COUNTIF(movement_type = 'contraction')      AS contracted_customers,
35        COUNTIF(ending_mrr > 0)                     AS ending_customers,
36        LOGICAL_OR(is_current_month)                AS is_current_month
37    FROM {{core.recurly.mrr_by_customer_month}}
38    GROUP BY 1, 2
39)
40