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.…