MRR Movements: New, Expansion, Contraction and Churn

Hard
422.

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

ColumnType
account_idint
month_startdate
mrrint

Primary key (together): 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).

Example

The rows must come back in the order shown.

account_mrr

account_idmonth_startmrr
12024-01-014999
22024-01-019999
32024-01-0124999
12024-02-014999
22024-02-0114999
42024-02-019999
12024-03-012999
22024-03-0114999
32024-03-0124999
42024-03-019999
2 more rows

Output

month_startnew_mrrexpansion_mrrcontraction_mrrchurned_mrr
2024-02-0199995000024999
2024-03-0124999020000
2024-04-0105000034998
2024-05-0100022998
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
month_startdate
mrrint

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_idmonth_startmrr
12024-01-014999
22024-01-019999
32024-01-0124999
12024-02-014999
22024-02-0114999
42024-02-019999
12024-03-012999
22024-03-0114999
32024-03-0124999
42024-03-019999
12024-04-012999
22024-04-0119999

Output

month_startnew_mrrexpansion_mrrcontraction_mrrchurned_mrr
2024-02-0199995000024999
2024-03-0124999020000
2024-04-0105000034998
2024-05-0100022998

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

Another 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 →