How to Calculate MRR and ARR Directly from Your Database

Every SaaS founder has been there: the board meeting is in two hours, someone asks for "current MRR by plan tier," and you're opening a spreadsheet you updated three weeks ago and hoping nothing has changed. The real numbers are in your database. Getting them out, traditionally, means asking an engineer to write a query or waiting until the next sprint.

This guide walks through exactly how MRR and ARR are calculated from raw subscription data, what queries look like, and how tools like AI for Database let you pull live numbers in plain English no SQL, no engineering queue.

What MRR and ARR Actually Measure

Monthly Recurring Revenue (MRR) is the predictable monthly revenue from active subscriptions. Annual Recurring Revenue (ARR) is simply MRR × 12. These are the north-star metrics for any subscription business because they strip out one-time payments, refunds, and noise leaving only the revenue you can count on.

The tricky part is that "active subscriptions" is not a static list. Customers upgrade, downgrade, pause, churn, and reactivate constantly. Any MRR figure more than a day old is potentially stale. That's why you want to calculate it directly from your database, not from a snapshot in a spreadsheet.

The key components of MRR are:

What Your Database Schema Typically Looks Like

Most subscription businesses store billing data in one of two patterns: a subscriptions table with current state, or an invoices / payments table that records every billing event. Often you have both.

A typical subscriptions table might look like this:

CREATE TABLE subscriptions (
  id           UUID PRIMARY KEY,
  customer_id  UUID NOT NULL,
  plan_id      VARCHAR(50),
  status       VARCHAR(20), , 'active', 'canceled', 'paused', 'trialing'
  mrr_cents    INTEGER,     , current monthly value in cents
  started_at   TIMESTAMP,
  canceled_at  TIMESTAMP,
  updated_at   TIMESTAMP
);

And a subscription_events table that logs state changes:

CREATE TABLE subscription_events (
  id              UUID PRIMARY KEY,
  subscription_id UUID,
  event_type      VARCHAR(50), , 'created', 'upgraded', 'downgraded', 'canceled'
  mrr_before      INTEGER,
  mrr_after       INTEGER,
  occurred_at     TIMESTAMP
);

If you use Stripe, most of these fields map directly to their API objects. If you use a custom billing system, the names differ but the structure is similar.

Calculating Current MRR with SQL

Here is the core query for current MRR from a subscriptions table:

SELECT
  plan_id,
  COUNT(*) AS active_subscribers,
  SUM(mrr_cents) / 100.0 AS mrr_usd
FROM subscriptions
WHERE status = 'active'
GROUP BY plan_id
ORDER BY mrr_usd DESC;

For total MRR across all plans:

SELECT
  SUM(mrr_cents) / 100.0 AS total_mrr_usd,
  SUM(mrr_cents) / 100.0 * 12 AS arr_usd
FROM subscriptions
WHERE status = 'active';

If your billing is annual, make sure you normalize annual subscriptions to a monthly value before summing. A customer paying $1,200/year contributes $100/month to MRR.

SELECT
  SUM(
    CASE
      WHEN billing_interval = 'year' THEN annual_amount_cents / 12.0
      WHEN billing_interval = 'month' THEN monthly_amount_cents
      ELSE 0
    END
  ) / 100.0 AS mrr_usd
FROM subscriptions
WHERE status = 'active';

Calculating MRR Movement (New, Expansion, Churned)

This is where things get more interesting and where most spreadsheet-based tracking falls apart. To calculate MRR movements for a given month, you compare subscription states at the start and end of the period.

WITH start_of_month AS (
  SELECT subscription_id, SUM(mrr_after) AS mrr
  FROM subscription_events
  WHERE occurred_at < '2026-03-01'
  GROUP BY subscription_id
),
end_of_month AS (
  SELECT subscription_id, SUM(mrr_after) AS mrr
  FROM subscription_events
  WHERE occurred_at < '2026-04-01'
  GROUP BY subscription_id
),
movement AS (
  SELECT
    COALESCE(e.subscription_id, s.subscription_id) AS subscription_id,
    COALESCE(s.mrr, 0) AS mrr_start,
    COALESCE(e.mrr, 0) AS mrr_end
  FROM start_of_month s
  FULL OUTER JOIN end_of_month e ON s.subscription_id = e.subscription_id
)
SELECT
  SUM(CASE WHEN mrr_start = 0 AND mrr_end > 0 THEN mrr_end ELSE 0 END) / 100.0 AS new_mrr,
  SUM(CASE WHEN mrr_start > 0 AND mrr_end > mrr_start THEN mrr_end - mrr_start ELSE 0 END) / 100.0 AS expansion_mrr,
  SUM(CASE WHEN mrr_start > 0 AND mrr_end < mrr_start AND mrr_end > 0 THEN mrr_start - mrr_end ELSE 0 END) / 100.0 AS contraction_mrr,
  SUM(CASE WHEN mrr_start > 0 AND mrr_end = 0 THEN mrr_start ELSE 0 END) / 100.0 AS churned_mrr
FROM movement;

These queries work, but they take time to write, test, and maintain. Every time your schema changes, someone has to update the SQL.

Asking Your Database in Plain English

This is where a tool like AI for Database changes the workflow entirely. Instead of writing those queries from scratch, you connect your database and type questions like:

The tool translates each question into SQL, runs it against your live database, and returns the answer as a table or chart. You don't need to know the table names or figure out the join logic.

For recurring metrics like this, you can also set up a self-refreshing dashboard. Pin the MRR breakdown query to a dashboard, and it recalculates automatically every hour or every morning before your standup. No more "let me pull the latest numbers" before every meeting.

Common MRR Calculation Mistakes to Avoid

Including trials in MRR. Trial users aren't paying yet. Filter them out with WHERE status = 'active' and make sure "active" in your system means "paying."

Counting annual subscriptions at face value. If a customer pays $2,400 upfront for a year, booking it all as one month's MRR makes your chart look like a roller coaster. Normalize to monthly.

Double-counting upgrades. If a customer upgrades mid-month, some billing systems create a new subscription record rather than updating the existing one. You may need to deduplicate by customer before summing.

Not accounting for paused subscriptions. If your product lets users pause billing, they're not churned but they're also not contributing to MRR. Most SaaS metrics definitions exclude them from active MRR.

Using invoice data instead of subscription data. Invoices reflect when you bill, not what the customer is currently paying. A customer who paid annually in January won't have an invoice in March, but they're still contributing to MRR.

Treating MRR as a Live Metric

The biggest shift in how founders think about MRR is treating it as a live metric rather than a monthly exercise. When your database is the source of truth and you can query it in plain English, MRR goes from a report you prepare to a number you just know.

A dashboard that shows current MRR, MRR by plan, and last 30 days of movement refreshed automatically, visible to your whole leadership team creates a shared understanding of the business that no spreadsheet can replicate.

Try AI for Database free at aifordatabase.com. Connect your billing database, ask for your current MRR, and have a live dashboard running before your next team meeting.