Menu

My churn query showed 0 churn every month. It was a missing-row problem

#sql#redditsql#month#prev#customer
Reading 0:00
15s threshold

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

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.