MRR movements

1-- core.chartmogul.mrr_movements
2-- The MRR bridge, as ChartMogul's "MRR movements" chart shows it: opening MRR,
3-- new business, expansion, contraction, churn, reactivation, closing MRR.
4-- Grain: month x currency. Every month from the first activity to the current
5-- month is present, including months without movements.
6--
7-- closing_mrr is the running sum of every movement since the first activity, so
8-- it only equals real MRR when the activity history is complete (the first
9-- sync pulls all of it). chartmogul_reported_mrr is ChartMogul's own MRR at
10-- the last synced day of the month, summed over plans, for reconciliation. The
11-- two should agree; a gap usually means ChartMogul restated history after a sync.
12--
13-- ChartMogul reports every amount in the single account currency, so expect one
14-- currency. chartmogul_reported_mrr carries no currency and is joined on month
15-- alone; it is in the same account currency.
16--
17-- Amounts in major units. Contraction and churn are negative.
18
19WITH movements AS (
20    SELECT
21        month,
22        currency,
23        SUM(new_business_mrr)        AS new_business_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(other_mrr)               AS other_mrr,
29        SUM(net_mrr_movement)        AS net_mrr_movement,
30        COUNTIF(new_business_activities > 0) AS new_customers,
31        COUNTIF(churn_activities > 0)        AS churned_customers,
32        COUNTIF(reactivation_activities > 0) AS reactivated_customers
33    FROM {{core.chartmogul.customer_mrr_movements}}
34    GROUP BY month, currency
35),
36
37month_spine AS (
38    SELECT currency, month
39    FROM (
40        SELECT currency, MIN(month) AS first_month