How to calculate MRR in SQL

Monthly recurring revenue is the number a subscription business is judged on, and it is deceptively hard to query. The arithmetic is trivial. Deciding what belongs in it is not.

This guide covers the query itself, then the four decisions that change the answer.

The shape of the data

Assume a subscriptions table:

Current MRR

The simplest useful version: what is recurring right now.

PostgreSQL

SELECT
  SUM(
    CASE interval
      WHEN 'month' THEN amount
      WHEN 'year'  THEN amount / 12.0
    END
  ) AS mrr
FROM subscriptions
WHERE status = 'active';

MySQL

SELECT
  SUM(
    CASE `interval`
      WHEN 'month' THEN amount
      WHEN 'year'  THEN amount / 12.0
    END
  ) AS mrr
FROM subscriptions
WHERE status = 'active';

interval is a reserved word in MySQL, so it needs backticks. The 12.0 rather than 12 matters in both: integer division will quietly truncate an annual plan of 1,199 to 99 instead of 99.92.

MRR over time

A single number tells you nothing about direction. For a monthly series you need, for each month, the subscriptions that were active during it.

-- PostgreSQL
SELECT
  m.month,
  SUM(
    CASE s.interval
      WHEN 'month' THEN s.amount
      WHEN 'year'  THEN s.amount / 12.0
    END
  ) AS mrr
FROM generate_series(
       DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months'),
       DATE_TRUNC('month', CURRENT_DATE),
       INTERVAL '1 month'
     ) AS m(month)
LEFT JOIN subscriptions s
  ON s.started_at < m.month + INTERVAL '1 month'
 AND (s.canceled_at IS NULL OR s.canceled_at >= m.month)
GROUP BY m.month
ORDER BY m.month;

The join condition is the whole query. A subscription counts towards a month if it started before that month ended, and had not already been cancelled when the month began. Get either half wrong and the series drifts without ever looking obviously broken.

MySQL has no generate_series. On 8.0 you can build the month list with a recursive CTE:

WITH RECURSIVE months AS (
  SELECT DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m-01') AS month
  UNION ALL
  SELECT DATE_ADD(month, INTERVAL 1 MONTH) FROM months
  WHERE month < DATE_FORMAT(CURDATE(), '%Y-%m-01')
)
SELECT
  m.month,
  SUM(
    CASE s.`interval`
      WHEN 'month' THEN s.amount
      WHEN 'year'  THEN s.amount / 12.0
    END
  ) AS mrr
FROM months m
LEFT JOIN subscriptions s
  ON s.started_at < DATE_ADD(m.month, INTERVAL 1 MONTH)
 AND (s.canceled_at IS NULL OR s.canceled_at >= m.month)
GROUP BY m.month
ORDER BY m.month;

Four decisions that change the number

Annual plans

An annual subscription is not twelve months of revenue in the month it was sold. Dividing by twelve, as above, is the standard treatment, and it is why MRR and cash collected never match. If someone asks why the bank balance disagrees with the dashboard, this is usually the reason.

Who counts as active

status = 'active' is the obvious filter and it hides a decision. A past_due subscription has not been cancelled, and the customer may well pay next week. Including them flatters the number; excluding them understates it. Either is defensible, but the choice has to be the same every month or the trend is meaningless.

One-off payments do not belong

Setup fees, overage charges and professional services are revenue, but they are not recurring. If they live in the same table as subscriptions, they need excluding, otherwise a single large implementation fee shows up as permanent growth.

Discounts

A customer on a 20% discount contributes 80% of list price to MRR. If the discount lives in a separate column or a coupons table, the amount above is wrong for exactly those customers, which tend to be the largest ones.

Related movements

Once MRR itself is right, the interesting figures are the movements between months: new, expansion, contraction and churned. Each is the same query with a different join condition, comparing a subscription’s state in consecutive months.

Or just ask for it

Every query here assumes your table is laid out as described, that interval holds exactly those two values, and that status = 'active' means paying. On a real schema that is the part nobody can write from memory.

ByeSQL reads your actual schema and writes the query against it, so “MRR by month for the last year, annual plans normalised” returns a result with the SQL next to it. When it guesses wrong about what your columns mean, you can see the guess rather than inherit it.

→ How to get revenue by month in SQL