Fct Mrr

1-- Monthly MRR + the movement bridge (new / expansion / contraction / churn), built on fct_revenue_item.
2-- A dense customer x month spine is essential: without a row for every month, lag() would compare a
3-- customer's last active month to their NEXT active month and silently skip the churn in between.
4-- Validation identity: starting_mrr + new + expansion - contraction - churn = ending_mrr.
5{{ config(materialized='table') }}
6
7with monthly as (
8    select
9        customer_id,
10        month,
11        sum(amount) as mrr
12    from {{ ref('fct_revenue_item') }}
13    where is_recurring
14    group by 1, 2
15),
16months as (
17    select distinct month from monthly  -- observed month range (swap for a generate_series spine if needed)
18),
19customers as (
20    select distinct customer_id from monthly
21),
22filled as (
23    -- one row per customer per month, zero-filled, so churn shows up as mrr -> 0
24    select
25        c.customer_id,
26        m.month,
27        coalesce(mo.mrr, 0) as mrr
28    from customers c
29    cross join months m
30    left join monthly mo
31        on mo.customer_id = c.customer_id
32       and mo.month = m.month
33),
34with_prev as (
35    select
36        customer_id,
37        month,
38        mrr,
39        lag(mrr) over (partition by customer_id order by month) as prev_mrr
40    from filled
41)
42select
43    month,
44    sum(mrr)                                                               as mrr,
45    sum(mrr) * 12                                                          as arr,
46    sum(case when coalesce(prev_mrr, 0) = 0 and mrr > 0 then mrr else 0 end)      as new_mrr,
47    sum(case when prev_mrr > 0 and mrr > prev_mrr then mrr - prev_mrr else 0 end) as expansion_mrr,
48    sum(case when mrr > 0 and mrr < prev_mrr then prev_mrr - mrr else 0 end)      as contraction_mrr,
49    sum(case when prev_mrr > 0 and mrr = 0 then prev_mrr else 0 end)              as churned_mrr
50from with_prev
51group by month
52order by month