MRR Movements: New, Expansion, Contraction and Churn
The SaaS metrics pack breaks each month's MRR change into four movements by comparing every account's MRR with the previous calendar month.
- Difficulty: Hard
- Topics: Joins, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #422
Problem statement
The SaaS metrics pack breaks each month's MRR change into four movements by comparing every account's MRR with the previous calendar month. An account with no row in a month paid nothing that month (MRR 0).
new_mrr: accounts with MRR this month and none last month — their whole MRR (this includes reactivations);expansion_mrr: accounts paying in both months, more now — the increase;contraction_mrr: accounts paying in both months, less now — the decrease, as a positive number;churned_mrr: accounts paying last month and nothing this month — last month's MRR.
Return one row per month from 2024-02-01 to 2024-05-01 (month_start), with the four columns (0 when nothing moved), ordered by month_start. Snapshots cover January to May 2024.
Tables
Table: account_mrr
| Column | Type |
|---|---|
| account_id | int |
| month_start | date |
| mrr | int |
Primary key: account_id, month_start.
One row per account per month it paid; month_start is always the first of a month between 2024-01-01 and 2024-05-01, mrr is positive (rupees).
Examples
Example 1
account_mrr
| account_id | month_start | mrr |
|---|---|---|
| 1 | 2024-01-01 | 4999 |
| 2 | 2024-01-01 | 9999 |
| 3 | 2024-01-01 | 24999 |
| 1 | 2024-02-01 | 4999 |
| 2 | 2024-02-01 | 14999 |
| 4 | 2024-02-01 | 9999 |
| 1 | 2024-03-01 | 2999 |
| 2 | 2024-03-01 | 14999 |
| 3 | 2024-03-01 | 24999 |
| 4 | 2024-03-01 | 9999 |
| 1 | 2024-04-01 | 2999 |
| 2 | 2024-04-01 | 19999 |
Output
| month_start | new_mrr | expansion_mrr | contraction_mrr | churned_mrr |
|---|---|---|---|---|
| 2024-02-01 | 9999 | 5000 | 0 | 24999 |
| 2024-03-01 | 24999 | 0 | 2000 | 0 |
| 2024-04-01 | 0 | 5000 | 0 | 34998 |
| 2024-05-01 | 0 | 0 | 0 | 22998 |
How to solve MRR Movements: New, Expansion, Contraction and Churn
Every movement compares an account's MRR in month M with month M − 1, and two of them are about rows that are absent: new MRR has no previous row and churn has no current row. A single join from either side can see only one of those, so the comparison set is built from both directions — the pattern a FULL OUTER JOIN would give, which MySQL lacks.
The solution generates the reporting months with a bounded recursive CTE (February to May, DATE_ADD(m, INTERVAL 1 MONTH)). Then pairs is a UNION ALL of (a) each current row LEFT JOINed to the same account's row of the previous month — prev_mrr NULL means new — and (b) each previous-month row with no current row (NOT EXISTS), which is churn. Conditional sums classify every pair: increase, decrease, whole new amount, whole lost amount; an unchanged account adds to nothing. Months are LEFT JOINed to the pairs and COALESCE turns empty sums into 0, so a quiet month still reports zeros.
The alternative densifies instead: cross join every account with every month from January, fill missing months with 0, and take LAG(mrr) per account. With zeros in place, new is prev = 0 AND mrr > 0 and churn is prev > 0 AND mrr = 0, and one window pass replaces the two-sided join. Densifying is the standard warehouse approach — it costs accounts × months rows, which is small here and keeps the logic in one place.
Reference solution (MySQL)
WITH RECURSIVE months AS (
SELECT CAST('2024-02-01' AS DATE) AS m
UNION ALL
SELECT DATE_ADD(m, INTERVAL 1 MONTH) FROM months WHERE m < '2024-05-01'
), pairs AS (
SELECT mo.m, cur.mrr AS cur_mrr, prev.mrr AS prev_mrr
FROM months mo
JOIN account_mrr cur ON cur.month_start = mo.m
LEFT JOIN account_mrr prev ON prev.account_id = cur.account_id AND prev.month_start = DATE_SUB(mo.m, INTERVAL 1 MONTH)
UNION ALL
SELECT mo.m, NULL, prev.mrr
FROM months mo
JOIN account_mrr prev ON prev.month_start = DATE_SUB(mo.m, INTERVAL 1 MONTH)
WHERE NOT EXISTS (SELECT 1 FROM account_mrr cur WHERE cur.account_id = prev.account_id AND cur.month_start = mo.m)
)
SELECT mo.m AS month_start,
COALESCE(SUM(CASE WHEN p.prev_mrr IS NULL THEN p.cur_mrr END), 0) AS new_mrr,
COALESCE(SUM(CASE WHEN p.cur_mrr > p.prev_mrr THEN p.cur_mrr - p.prev_mrr END), 0) AS expansion_mrr,
COALESCE(SUM(CASE WHEN p.cur_mrr < p.prev_mrr THEN p.prev_mrr - p.cur_mrr END), 0) AS contraction_mrr,
COALESCE(SUM(CASE WHEN p.cur_mrr IS NULL THEN p.prev_mrr END), 0) AS churned_mrr
FROM months mo
LEFT JOIN pairs p ON p.m = mo.m
GROUP BY mo.m
ORDER BY mo.mAnother way
WITH RECURSIVE months AS (
SELECT CAST('2024-01-01' AS DATE) AS m
UNION ALL
SELECT DATE_ADD(m, INTERVAL 1 MONTH) FROM months WHERE m < '2024-05-01'
), grid AS (
SELECT a.account_id, mo.m, COALESCE(x.mrr, 0) AS mrr
FROM (SELECT DISTINCT account_id FROM account_mrr) a
CROSS JOIN months mo
LEFT JOIN account_mrr x ON x.account_id = a.account_id AND x.month_start = mo.m
), moves AS (
SELECT m, mrr, LAG(mrr) OVER (PARTITION BY account_id ORDER BY m) AS prev FROM grid
)
SELECT mo.m AS month_start,
SUM(CASE WHEN v.prev = 0 AND v.mrr > 0 THEN v.mrr ELSE 0 END) AS new_mrr,
SUM(CASE WHEN v.prev > 0 AND v.mrr > v.prev THEN v.mrr - v.prev ELSE 0 END) AS expansion_mrr,
SUM(CASE WHEN v.mrr > 0 AND v.mrr < v.prev THEN v.prev - v.mrr ELSE 0 END) AS contraction_mrr,
SUM(CASE WHEN v.prev > 0 AND v.mrr = 0 THEN v.prev ELSE 0 END) AS churned_mrr
FROM months mo
LEFT JOIN moves v ON v.m = mo.m
WHERE mo.m >= '2024-02-01'
GROUP BY mo.m
ORDER BY mo.m← Median Days From Trial to Paid by Acquisition Channel · Verified Creators With Over One Lakh Followers →