MRR movements

1-- core.younium.mrr_movements
2-- The CMRR bridge: opening committed MRR, new customers, expansion, contraction,
3-- churn, reactivation, closing CMRR. Recreates the movement breakdown of
4-- Younium's CMRR reporting from its bookings.
5-- Grain: month. Every month from the first booking to the current month is
6-- present, including months without bookings.
7--
8-- Currency: everything is in the Younium base currency, using Younium's own
9-- conversion on each booking. base_currency is read from the accounts table.
10--
11-- closing_cmrr is the running sum of every booking since the first one, so it only
12-- equals real CMRR when the booking history is complete. For the current month,
13-- younium_current_cmrr is the sum of Younium's live CMRR over all accounts, for
14-- reconciliation.
15
16WITH movements AS (
17    SELECT
18        month,
19        SUM(new_customer_cmrr)  AS new_customer_cmrr,
20        SUM(expansion_cmrr)     AS expansion_cmrr,
21        SUM(contraction_cmrr)   AS contraction_cmrr,
22        SUM(churned_cmrr)       AS churned_cmrr,
23        SUM(reactivation_cmrr)  AS reactivation_cmrr,
24        SUM(other_cmrr)         AS other_cmrr,
25        SUM(net_cmrr_change)    AS net_cmrr_change,
26        SUM(net_acv_change)     AS net_acv_change,
27        COUNTIF(new_customer_bookings > 0) AS orders_from_new_customers,
28        COUNTIF(churn_bookings > 0)        AS orders_churned,
29        COUNTIF(reactivation_bookings > 0) AS orders_reactivated
30    FROM {{core.younium.subscription_mrr_movements}}
31    GROUP BY month
32),
33
34month_spine AS (
35    SELECT month
36    FROM UNNEST(GENERATE_DATE_ARRAY(
37        (SELECT MIN(month) FROM movements),
38        DATE_TRUNC(CURRENT_DATE(), MONTH),
39        INTERVAL 1 MONTH
40    )) AS month