How to Build a Revenue Waterfall Chart in SQL

Total MRR is one number. It can go up, it can go down, and when it goes down nobody in the room can explain why without pulling up a spreadsheet. The revenue waterfall fixes that. It decomposes MRR change into the four movements that actually matter: New, Expansion, Contraction, Churn. Add an optional fifth — Reactivation — and you've got a complete picture of where your revenue is coming from and where it's leaking.

This is one of the harder SaaS SQL queries to get right. Most published versions have subtle bugs around customers who change plans multiple times in a month, or around the churned-and-came-back edge case. The query below handles both.

The month-pair approach

The core idea: compare each customer's MRR this month to their MRR last month. The delta tells you which bucket they fall into.

• New: customer had no MRR last month, has MRR this month (and has never appeared before)
• Expansion: customer's MRR increased
• Contraction: customer's MRR decreased but is still positive
• Churn: customer had MRR last month, has zero (or no row) this month
• Reactivation: customer had no MRR last month, has MRR this month, but has appeared in a prior month

You need a table with one row per customer per month. If you're working from raw Stripe subscriptions, see How to Calculate Real MRR from the Stripe API for the normalization step. For the rest of this post, I'll assume you have:

-- customer_mrr(customer_id, month, mrr)
-- One row per customer per active month. month is a DATE (first of month).
-- Customers with $0 MRR should NOT have a row — absence = no revenue.

This table is the foundation for most SaaS metrics. The NRR calculation, cohort retention, and LTV analysis all start here.

The complete waterfall query

WITH months AS (
  SELECT DISTINCT month FROM customer_mrr
),
-- Full outer join: catches both churn (prev exists, curr doesn't)
-- and new/reactivation (curr exists, prev doesn't)
paired AS (
  SELECT
    COALESCE(curr.month, prev.month + INTERVAL '1 month') AS month,
    COALESCE(curr.customer_id, prev.customer_id)          AS customer_id,
    COALESCE(prev.mrr, 0)                                 AS prev_mrr,
    COALESCE(curr.mrr, 0)                                 AS curr_mrr
  FROM customer_mrr prev
  FULL OUTER JOIN customer_mrr curr
    ON  prev.customer_id = curr.customer_id
    AND curr.month = prev.month + INTERVAL '1 month'
  WHERE COALESCE(prev.mrr, 0) > 0
     OR COALESCE(curr.mrr, 0) > 0
),
-- Detect whether a customer has ever had MRR before this month
first_appearance AS (
  SELECT customer_id, MIN(month) AS first_month
  FROM customer_mrr
  GROUP BY customer_id
),
classified AS (
  SELECT
    p.month,
    p.customer_id,
    p.prev_mrr,
    p.curr_mrr,
    CASE
      WHEN p.prev_mrr = 0 AND p.curr_mrr > 0 AND f.first_month = p.month
        THEN 'new'
      WHEN p.prev_mrr = 0 AND p.curr_mrr > 0 AND f.first_month < p.month
        THEN 'reactivation'
      WHEN p.curr_mrr > p.prev_mrr AND p.prev_mrr > 0
        THEN 'expansion'
      WHEN p.curr_mrr < p.prev_mrr AND p.curr_mrr > 0
        THEN 'contraction'
      WHEN p.curr_mrr = 0 AND p.prev_mrr > 0
        THEN 'churn'
      ELSE 'unchanged'
    END AS movement_type
  FROM paired p
  LEFT JOIN first_appearance f ON p.customer_id = f.customer_id
)
 
SELECT
  month,
  SUM(CASE WHEN movement_type = 'new'
      THEN curr_mrr ELSE 0 END)                           AS new_mrr,
  SUM(CASE WHEN movement_type = 'reactivation'
      THEN curr_mrr ELSE 0 END)                           AS reactivation_mrr,
  SUM(CASE WHEN movement_type = 'expansion'
      THEN curr_mrr - prev_mrr ELSE 0 END)                AS expansion_mrr,
  SUM(CASE WHEN movement_type = 'contraction'
      THEN prev_mrr - curr_mrr ELSE 0 END)                AS contraction_mrr,
  SUM(CASE WHEN movement_type = 'churn'
      THEN prev_mrr ELSE 0 END)                           AS churned_mrr,
  -- Net new = new + reactivation + expansion - contraction - churn
  SUM(CASE WHEN movement_type = 'new' THEN curr_mrr
           WHEN movement_type = 'reactivation' THEN curr_mrr
           WHEN movement_type = 'expansion' THEN curr_mrr - prev_mrr
           WHEN movement_type = 'contraction' THEN -(prev_mrr - curr_mrr)
           WHEN movement_type = 'churn' THEN -prev_mrr
           ELSE 0 END)                                     AS net_new_mrr,
  SUM(curr_mrr)                                            AS ending_mrr
FROM classified
WHERE month IN (SELECT month FROM months)
GROUP BY month
ORDER BY month;

A few things worth calling out. The FULL OUTER JOIN is doing the heavy lifting — a LEFT JOIN from previous to current catches churn but misses brand-new customers who had no previous-month row. The first_appearance CTE separates genuinely new customers from reactivations. And the WHERE month IN (SELECT month FROM months) filters out phantom future months generated by the prev.month + INTERVAL '1 month' arithmetic.

The same-month upgrade-and-downgrade gotcha

A customer on the $50 plan upgrades to $200 mid-month, then downgrades to $100 before month-end. Your billing system might record two plan changes. If you classify each change independently, you'd show $150 expansion and $100 contraction, which nets to $50 but overstates both columns.

The month-pair approach sidesteps this entirely because it only compares the final MRR at the end of each month. Customer started the month at $50, ended at $100 — that's $50 expansion. One movement, one number, no double-counting. This is why you aggregate to customer_mrr at month boundaries first, not at the individual subscription-change level.

The sanity check you should always run

Every waterfall should pass this identity:

starting_mrr + net_new_mrr = ending_mrr

If it doesn't, your query has a bug. Here's the check:

WITH waterfall AS (
  -- (paste the full query above)
),
running AS (
  SELECT
    month,
    ending_mrr,
    net_new_mrr,
    LAG(ending_mrr) OVER (ORDER BY month) AS prev_ending_mrr
  FROM waterfall
)
 
SELECT
  month,
  prev_ending_mrr                                    AS starting_mrr,
  net_new_mrr,
  prev_ending_mrr + net_new_mrr                      AS expected_ending,
  ending_mrr                                         AS actual_ending,
  ending_mrr - (prev_ending_mrr + net_new_mrr)       AS discrepancy
FROM running
WHERE prev_ending_mrr IS NOT NULL
  AND ABS(ending_mrr - (prev_ending_mrr + net_new_mrr)) > 0.01;

If this returns any rows, something is wrong. The most common cause: customers with $0 MRR still having rows in your customer_mrr table. Remove them or filter to mrr > 0 in the base CTE.

Handling annual contracts

Annual contracts need normalization before they enter the waterfall. A customer paying $12,000/year is $1,000/month in MRR terms. If you don't normalize, a single annual renewal spikes your "new" or "expansion" bucket by 12x.

Build the normalization into your customer_mrr table:

-- Build customer_mrr from raw subscriptions, normalizing annual to monthly
INSERT INTO customer_mrr (customer_id, month, mrr)
SELECT
  s.customer_id,
  gs.month,
  CASE
    WHEN s.billing_interval = 'year'
      THEN s.plan_amount / 12.0
    WHEN s.billing_interval = 'quarter'
      THEN s.plan_amount / 3.0
    ELSE s.plan_amount
  END AS mrr
FROM subscriptions s
CROSS JOIN LATERAL generate_series(
  DATE_TRUNC('month', s.created),
  DATE_TRUNC('month', COALESCE(s.canceled_at, CURRENT_DATE)),
  INTERVAL '1 month'
) AS gs(month)
WHERE s.status IN ('active', 'past_due', 'canceled')
GROUP BY s.customer_id, gs.month
-- If a customer has multiple subs, sum them
HAVING SUM(
  CASE
    WHEN s.billing_interval = 'year' THEN s.plan_amount / 12.0
    WHEN s.billing_interval = 'quarter' THEN s.plan_amount / 3.0
    ELSE s.plan_amount
  END
) > 0;

The generate_series expands each subscription into its active months. A customer with a $6,000 annual plan created in January and canceled in October gets rows for Jan through Oct, each with $500 MRR. The waterfall query doesn't need to know the original billing interval at all.

One thing to watch: if you're on MySQL, generate_series isn't available. You'll need a calendar table or a recursive CTE to produce the month series. DuckDB and Postgres both support it natively.

Mid-month proration

When a customer upgrades from $100 to $200 on the 15th of the month, the invoice for that month might show $100 (first half) + $100 prorated (second half) = $200 total. But the MRR is $200 going forward, not $200 for that transitional month.

The rule: MRR is always the recurring rate as of month-end, not the amount actually invoiced. Invoices include one-time adjustments, proration credits, and setup fees that aren't recurring. Mixing invoice amounts into MRR is the second most common waterfall bug after the double-counting problem.

If you're pulling from Stripe, use subscription.items.price.unit_amount (the current recurring price), not invoice.amount_paid. The Stripe subscription analysis post goes deeper on extracting clean recurring amounts from Stripe's data model.

From query to dashboard

The waterfall query gives you a table. What your CFO wants is a stacked bar chart where green bars go up (new, expansion, reactivation) and red bars go down (contraction, churn), with a net line showing the trend. That's the bridge chart.

Fastero's Stripe integration syncs your subscription data and lets you run the waterfall query directly against it in the SQL editor. From there, you can pin the results to a dashboard widget — stacked bar for the movements, number tile for current MRR. Pair it with the revenue leak detection module and you'll get alerts when churn or contraction spikes, rather than discovering it next month when someone asks why MRR is flat.

The waterfall is one of those queries worth maintaining carefully. When the number matches Stripe's dashboard and your finance team's spreadsheet, you stop having the "which MRR is right" conversation entirely.

Try Fastero free — connect Stripe, build MRR waterfall dashboards, and get alerts when revenue movements shift. No credit card required.