Waterfall de MRR: new, expansion, contraction y churn en un solo informe
Descomposición mensual del ingreso recurrente en cuatro movimientos mediante un FULL JOIN del MRR de cada cliente con su mes anterior — el informe estándar de cualquier SaaS, en SQL puro.
Requisitos
PostgreSQL 9.4+ (FILTER), table subscriptions (customer_id, period, mrr)
SQL
WITH mensuel AS (
SELECT customer_id, date_trunc('month', period) AS mois, SUM(mrr) AS mrr
FROM subscriptions
GROUP BY 1, 2
),
delta AS (
SELECT
COALESCE(cur.mois, prev.mois + interval '1 month') AS mois,
COALESCE(cur.mrr, 0) AS mrr_cur,
COALESCE(prev.mrr, 0) AS mrr_prev
FROM mensuel cur
FULL JOIN mensuel prev
ON prev.customer_id = cur.customer_id
AND prev.mois = cur.mois - interval '1 month'
)
SELECT
to_char(mois, 'YYYY-MM') AS mois,
SUM(mrr_cur) FILTER (WHERE mrr_prev = 0) AS new_mrr,
SUM(mrr_cur - mrr_prev) FILTER (WHERE mrr_prev > 0 AND mrr_cur > mrr_prev) AS expansion,
SUM(mrr_cur - mrr_prev) FILTER (WHERE mrr_cur > 0 AND mrr_cur < mrr_prev) AS contraction,
-SUM(mrr_prev) FILTER (WHERE mrr_cur = 0) AS churn,
SUM(mrr_cur - mrr_prev) AS variation_nette
FROM delta
GROUP BY mois
ORDER BY mois;Resultado
mois | new_mrr | expansion | contraction | churn | variation_nette ---------+---------+-----------+-------------+--------+----------------- 2026-02 | 4250 | 1830 | -420 | -1100 | 4560 2026-03 | 3900 | 2210 | -680 | -2450 | 2980 2026-04 | 5120 | 1540 | -310 | -890 | 5460 2026-05 | 2840 | 3110 | -1240 | -3780 | 930 (4 rows) Mai : l'expansion compense à peine un churn record (-3 780) — analyser les départs
SQLSaaSMRRMulti-CTEReporting