was doing a simple MRR breakdown (new / expansion / churn per month) and churn came out as 0 every single month. looked great for about a minute
small version of the data:
```
customer | month | mrr
A | 2025-01 | 100
A | 2025-02 | 100
A | 2025-03 | 100
B | 2025-01 | 50
B | 2025-02 | 80
B | 2025-03 | 80
C | 2025-01 | 70 <- cancels after january
D | 2025-03 | 40 <- new in march
```
total MRR is 220, 180, 220. so feb dropped by 40: B expanded +30 and C churned -70
**first attempt, LAG straight on the table**
```
SELECT month,
SUM(CASE WHEN prev IS NULL THEN mrr ELSE 0 END) AS new,
SUM(CASE WHEN mrr > prev THEN mrr - prev ELSE 0 END) AS expansion,
SUM(CASE WHEN mrr < prev THEN mrr - prev ELSE 0 END) AS churned
FROM (
SELECT month, mrr,
LAG(mrr) OVER (PARTITION BY customer ORDER BY month) AS prev
FROM invoices
) t
GROUP BY month
ORDER BY month;
```
feb comes out as new 0, expansion 30, churned 0. the -70 just isnt there
reason: C has no row in february. LAG can only compare rows that exist, so a customer who left never shows up in the month they left
**fix: build every customer x every month first**
```
WITH grid AS (
SELECT c.customer, m.month, COALESCE(i.mrr, 0) AS mrr
FROM (SELECT DISTINCT customer FROM invoices) c
CROSS JOIN (SELECT DISTINCT month FROM invoices) m
LEFT JOIN invoices i
ON i.customer = c.customer AND i.month = m.month
),
moves AS (
SELECT month, mrr,
LAG(mrr) OVER (PARTITION BY customer ORDER BY month) AS prev
FROM grid
)
SELECT month,
SUM(CASE WHEN prev = 0 AND mrr > 0 THEN mrr ELSE 0 END) AS new,
SUM(CASE WHEN prev > 0 AND mrr > prev THEN mrr - prev ELSE 0 END) AS expansion,
SUM(CASE WHEN prev > 0 AND mrr = 0 THEN -prev ELSE 0 END) AS churned,
SUM(mrr - prev) AS net_change
FROM moves
WHERE prev IS NOT NULL
GROUP BY month
ORDER BY month;
```
```
month | new | expansion | churned | net_change
2025-02 | 0 | 30 | -70 | -40
2025-03 | 40 | 0 | 0 | 40
```
now C becomes a 0 in feb and LAG sees 70 -> 0. and net_change matches the real total change (-40, +40), which is the check i do now every time: the parts have to add up to the total, if they dont something is missing
two things i'm still not sure about:
- in a real table, months with zero invoices for everyone would be missing from SELECT DISTINCT month. do you generate the months instead (generate_series / calendar table)?
- a customer who leaves and comes back later counts as "new" here. do you treat that as reactivation separately?
