Customer MRR movements

1-- core.chartmogul.customer_mrr_movements
2-- ChartMogul's MRR movements per customer per month: the drill-down behind the
3-- monthly MRR bridge.
4-- Grain: month x customer x currency. Month is the calendar month of the activity
5-- date in UTC.
6--
7-- These are ChartMogul's own classifications, not re-derived: each activity's type
8-- (new_biz, expansion, contraction, churn, reactivation) decides the column and
9-- its signed activity_mrr_movement is the amount. Contraction and churn are
10-- therefore negative, and net_mrr_movement is a plain sum.
11--
12-- Amounts are in major units of the ChartMogul account currency (staging divides
13-- ChartMogul's cents by 100).
14
15WITH activities AS (
16    SELECT
17        DATE_TRUNC(DATE(activity_at), MONTH) AS month,
18        customer_uuid,
19        currency,
20        activity_type,
21        mrr_movement
22    FROM {{staging.chartmogul.customer_activities}}
23),
24
25per_customer AS (
26    SELECT
27        month,
28        customer_uuid,
29        currency,
30        SUM(IF(activity_type = 'new_biz', mrr_movement, 0))      AS new_business_mrr,
31        SUM(IF(activity_type = 'expansion', mrr_movement, 0))    AS expansion_mrr,
32        SUM(IF(activity_type = 'contraction', mrr_movement, 0))  AS contraction_mrr,
33        SUM(IF(activity_type = 'churn', mrr_movement, 0))        AS churned_mrr,
34        SUM(IF(activity_type = 'reactivation', mrr_movement, 0)) AS reactivation_mrr,
35        SUM(IF(activity_type NOT IN ('new_biz', 'expansion', 'contraction', 'churn', 'reactivation')
36               OR activity_type IS NULL, mrr_movement, 0))      AS other_mrr,
37        SUM(mrr_movement)                                        AS net_mrr_movement,
38        COUNTIF(activity_type = 'new_biz')                       AS new_business_activities,
39        COUNTIF(activity_type = 'churn')                         AS churn_activities,
40        COUNTIF(activity_type = 'reactivation')                  AS reactivation_activities,