Subscription MRR movements

1-- core.younium.subscription_mrr_movements
2-- Younium's CMRR movements per subscription per month: the drill-down behind the
3-- monthly CMRR bridge.
4-- Grain: month x order number. Month is the calendar month of the booking's
5-- effective date (UTC).
6--
7-- Movements are Younium's own booking classifications, not re-derived from
8-- charges: NewCustomer, Expansion, Contraction, CustomerChurn and Reactivate map to
9-- the five columns; NoChange, SplitOrder and anything else go to other_cmrr.
10-- Amounts are the booking's CMRR change in the Younium base currency, signed as
11-- Younium records it (contraction and churn are expected to be negative; see
12-- tests/assert_younium__movement_signs_match_classification.sql).
13--
14-- The order-currency CMRR change is kept alongside, for orders billed in another
15-- currency.
16
17WITH bookings AS (
18    SELECT
19        DATE_TRUNC(DATE(effective_at), MONTH) AS month,
20        order_number,
21        order_currency,
22        classification_type,
23        cmrr_change,
24        cmrr_change_base,
25        acv_change_base
26    FROM {{staging.younium.booking}}
27    WHERE effective_at IS NOT NULL
28),
29
30per_order AS (
31    SELECT
32        month,
33        order_number,
34        -- An order keeps one currency for its lifetime; MAX picks it without
35        -- adding it to the grain.
36        MAX(order_currency)                                              AS order_currency,
37        SUM(IF(classification_type = 'NewCustomer', cmrr_change_base, 0))   AS new_customer_cmrr,
38        SUM(IF(classification_type = 'Expansion', cmrr_change_base, 0))     AS expansion_cmrr,
39        SUM(IF(classification_type = 'Contraction', cmrr_change_base, 0))   AS contraction_cmrr,
40        SUM(IF(classification_type = 'CustomerChurn', cmrr_change_base, 0)) AS churned_cmrr,