If you’ve ever been asked, “Can you quickly pull month-over-month revenue?” and your soul briefly left your body… same.

This is the good news: SQL window functions let you calculate finance KPIs cleanly, accurately, and without turning your query into a pile of nested subqueries that looks like it was written during a caffeine crisis.

In this post, I’m going to walk through a practical set of KPIs using windows:

• Monthly revenue
• MoM change and MoM %
• Rolling 30-day revenue
• Top merchants by revenue
• A few guardrails so your numbers don’t silently lie to you

No magic. No “trust me bro.” Just SQL that your future self can read without filing a complaint.

The Setup: A Simple Transactions Table

Let’s assume a table like this:

transactions

• transaction_id (unique id)
• user_id
• merchant
• category
• amount (positive for purchases, negative for refunds/chargebacks)
• currency
• status (e.g., posted, pending, reversed)
• transaction_ts (timestamp)

In finance land, it’s rarely “just sum the amount.” There are usually a few “fun surprises” like pending transactions, reversals, and refunds. So first:

Rule #1: Decide What Counts

For KPIs, I usually only count posted transactions (or whatever your system calls “finalized”).

-- Base filter: only count finalized transactions
WITH base AS (
  SELECT
    transaction_id,
    user_id,
    merchant,
    category,
    amount,
    currency,
    transaction_ts,
    DATE_TRUNC('day', transaction_ts) AS txn_date,
    DATE_TRUNC('month', transaction_ts) AS txn_month
  FROM transactions
  WHERE status = 'posted'
)
SELECT *
FROM base
LIMIT 10;

If your company counts pending too, you can include them… but then your KPI is basically “revenue, but with vibes.” Choose wisely.

1) Monthly Revenue

Let’s start simple:

WITH base AS (
  SELECT
    amount,
    DATE_TRUNC('month', transaction_ts) AS txn_month
  FROM transactions
  WHERE status = 'posted'
)
SELECT
  txn_month,
  SUM(amount) AS revenue
FROM base
GROUP BY 1
ORDER BY 1;

That’s the foundation. Now we make it actually useful.

2) MoM Change and MoM % (Without Manual Math ofc)

This is where LAG() earns its paycheck.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', transaction_ts) AS txn_month,
    SUM(amount) AS revenue
  FROM transactions
  WHERE status = 'posted'
  GROUP BY 1
)
SELECT
  txn_month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY txn_month) AS mom_change,
  CASE
    WHEN LAG(revenue) OVER (ORDER BY txn_month) = 0 THEN NULL
    ELSE (revenue - LAG(revenue) OVER (ORDER BY txn_month))
         / LAG(revenue) OVER (ORDER BY txn_month)
  END AS mom_pct
FROM monthly
ORDER BY txn_month;

Notes from the “don’t embarrass yourself in front of stakeholders” department:

• The first month has no previous month, so MoM is naturally NULL.
• If the previous month is 0, MoM % is undefined. Returning NULL is safer than dividing by zero and summoning chaos.

If you want the percent as a readable number:

ROUND(100 * mom_pct, 2) AS mom_pct

3) Rolling 30-Day Revenue (the “trend” KPI everyone loves)

Rolling metrics help smooth noise and show momentum. The trick is that there are two common versions:

1. Rolling 30 days by calendar date
2. Rolling 30 rows by transaction record (not what you want)

We want rolling by date. So we aggregate by day first, then window over days.

WITH daily AS (
  SELECT
    DATE_TRUNC('day', transaction_ts) AS txn_date,
    SUM(amount) AS daily_revenue
  FROM transactions
  WHERE status = 'posted'
  GROUP BY 1
)
SELECT
  txn_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY txn_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS rolling_30d_revenue
FROM daily
ORDER BY txn_date;

Why ROWS BETWEEN 29 PRECEDING?

Because after we’ve grouped by day, each row = one day. So 30 rows = 30 days.

If your dataset has missing days and you want a true “calendar” rolling window, you’d generate a date spine (calendar table) and left join your daily revenue onto it. That’s slightly more advanced, but also extremely professional. If you want, I’ll write that version too.

4) Top Merchants by Revenue

This is where DENSE_RANK() shines.

Let’s do it monthly: top merchants per month.

WITH merchant_monthly AS (
  SELECT
    DATE_TRUNC('month', transaction_ts) AS txn_month,
    merchant,
    SUM(amount) AS revenue
  FROM transactions
  WHERE status = 'posted'
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    txn_month,
    merchant,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY txn_month
      ORDER BY revenue DESC
    ) AS merchant_rank
  FROM merchant_monthly
)
SELECT
  txn_month,
  merchant,
  revenue,
  merchant_rank
FROM ranked
WHERE merchant_rank <= 5
ORDER BY txn_month, merchant_rank, revenue DESC;

This gives you the top 5 merchants each month without messy correlated subqueries.

5) A Finance Reality Check: Refunds and “Revenue That Isn’t Actually Revenue”

One of the fastest ways to produce confusing charts is to lump purchases and refunds together with no context.

A better approach: split them.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', transaction_ts) AS txn_month,
    SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS gross_sales,
    SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS refunds,
    SUM(amount) AS net_revenue
  FROM transactions
  WHERE status = 'posted'
  GROUP BY 1
)
SELECT
  txn_month,
  gross_sales,
  refunds,
  net_revenue,
  CASE
    WHEN gross_sales = 0 THEN NULL
    ELSE refunds / gross_sales
  END AS refund_rate
FROM monthly
ORDER BY txn_month;

This makes your reporting defensible:

• Gross sales = how much you sold
• Refunds = how much came back
• Net revenue = what you keep
• Refund rate = the “are we okay?” metric

6) KPI Guardrails: Data Quality Checks You Can Build Into SQL

You don’t need a whole monitoring platform to catch common issues. You can write simple checks that prevent bad data from quietly wrecking your graphs.

Check A: Duplicate Transaction IDs (the silent killer)

SELECT
  transaction_id,
  COUNT(*) AS cnt
FROM transactions
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

If this returns rows, your totals might be inflated. Fix duplicates before you publish anything.

Check B: Weird Future Timestamps

SELECT *
FROM transactions
WHERE transaction_ts > CURRENT_TIMESTAMP
ORDER BY transaction_ts DESC
LIMIT 50;

Future revenue is fun, but usually a bug.

Check C: Status Mix (why is “pending” in my numbers?)

SELECT
  status,
  COUNT(*) AS txn_count,
  SUM(amount) AS total_amount
FROM transactions
GROUP BY 1
ORDER BY txn_count DESC;

If someone “accidentally” included pending or reversed transactions in KPIs, this table will expose it immediately.

7) Put It Together: A Clean Monthly KPI Table (Ready for a Dashboard)

This is the kind of query that can feed a BI tool directly.

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', transaction_ts) AS txn_month,
    SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS gross_sales,
    SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) AS refunds,
    SUM(amount) AS net_revenue
  FROM transactions
  WHERE status = 'posted'
  GROUP BY 1
),
final AS (
  SELECT
    txn_month,
    gross_sales,
    refunds,
    net_revenue,
    net_revenue - LAG(net_revenue) OVER (ORDER BY txn_month) AS mom_change,
    CASE
      WHEN LAG(net_revenue) OVER (ORDER BY txn_month) = 0 THEN NULL
      ELSE (net_revenue - LAG(net_revenue) OVER (ORDER BY txn_month))
           / LAG(net_revenue) OVER (ORDER BY txn_month)
    END AS mom_pct
  FROM monthly
)
SELECT
  txn_month,
  gross_sales,
  refunds,
  net_revenue,
  mom_change,
  ROUND(100 * mom_pct, 2) AS mom_pct
FROM final
ORDER BY txn_month;

This is dashboard-friendly, recruiter-friendly, and future-you-friendly.

SQL That Makes Your KPIs Bulletproof (and Your Dashboards Less Embarrassing)

Window functions are one of those SQL features that feel like a cheat code, but the best part is they’re not just “fancy.” They’re practical.

When you can calculate MoM changes, rolling windows, and rankings directly in the query, you stop relying on fragile spreadsheets, manual filters, and “please don’t touch this tab” dashboards. Your metrics become repeatable, auditable, and easy to explain when someone inevitably asks, “Wait… why did revenue dip here?”

And that’s really the point: in finance, the numbers aren’t just numbers. They’re decisions, forecasts, and sometimes mildly panicked Slack messages. Using window functions well means your SQL doesn’t just pull data, it produces results people can trust.

So yes, you just learned how to do rolling 30-day revenue and MoM growth. But more importantly, you learned how to build KPIs in a way that won’t fall apart the second the data gets messy… which, as we know, is always.