How to Compare Month-over-Month Metrics in SQL

Every Monday standup has the same slide: this month's revenue vs. last month, with a green or red arrow. Building that from raw SQL takes five minutes. Building it so it doesn't lie to you takes more thought.

The queries below use two tables: orders(id, total, created_at, customer_id) for raw transactions, and monthly_revenue(month, revenue, cost, orders) for pre-aggregated numbers. If you don't have a monthly summary table yet, the multi-metric section shows how to build one on the fly from orders.

Basic MoM with LAG

select
  month,
  revenue,
  lag(revenue) over (order by month) as prev_month,
  revenue - lag(revenue) over (order by month) as abs_change,
  round(
    100.0 * (revenue - lag(revenue) over (order by month))
      / nullif(lag(revenue) over (order by month), 0),
    1
  ) as pct_change
from monthly_revenue
order by month;

LAG(revenue) OVER (ORDER BY month) grabs the previous row's value when rows are sorted chronologically. The first row returns NULL because there's nothing before it — that's correct, not a bug. NULLIF in the denominator prevents division by zero if your previous month happened to have zero revenue (it happens with new product lines).

One thing that trips people up: LAG with no second argument defaults to offset 1. That's one row back, which is one month back only if your table has no gaps. Skip February and suddenly March compares itself to January. If your data has gaps, fill them with a generate_series join before applying LAG, or use a self-join on the actual date instead.

The full comparison table

The view every CFO asks for: this month, last month, MoM %, same month last year, YoY %. One query.

select
  month,
  revenue,
  lag(revenue, 1) over (order by month)  as prev_month,
  round(
    100.0 * (revenue - lag(revenue, 1) over (order by month))
      / nullif(lag(revenue, 1) over (order by month), 0), 1
  ) as mom_pct,
  lag(revenue, 12) over (order by month) as same_month_ly,
  round(
    100.0 * (revenue - lag(revenue, 12) over (order by month))
      / nullif(lag(revenue, 12) over (order by month), 0), 1
  ) as yoy_pct
from monthly_revenue
where month >= date_trunc('month', current_date) - interval '24 months'
order by month;

LAG(revenue, 12) reaches back twelve rows — same month last year, assuming no gaps. When gaps are possible, a self-join on the calendar date is safer:

select m.month, m.revenue, ly.revenue as same_month_ly
from monthly_revenue m
left join monthly_revenue ly
  on ly.month = m.month - interval '1 year';

I use LAG when I control the data pipeline and can guarantee continuity. Self-join when I'm querying someone else's warehouse and can't be sure every month has a row.

Multi-metric MoM in one query

Revenue alone doesn't tell the story. Here's revenue, order count, unique customers, and average order value with MoM percentages, built from raw orders.

with monthly as (
  select
    date_trunc('month', created_at)::date as month,
    sum(total)                            as revenue,
    count(*)                              as orders,
    count(distinct customer_id)           as customers,
    round(sum(total) / nullif(count(*), 0), 2) as aov
  from orders
  group by 1
)
select
  month,
  revenue,
  round(100.0 * (revenue - lag(revenue) over w)
            / nullif(lag(revenue) over w, 0), 1)   as rev_mom,
  orders,
  round(100.0 * (orders - lag(orders) over w)
            / nullif(lag(orders) over w, 0), 1)    as ord_mom,
  customers,
  round(100.0 * (customers - lag(customers) over w)
            / nullif(lag(customers) over w, 0), 1) as cust_mom,
  aov
from monthly
window w as (order by month)
order by month;

WINDOW w AS (ORDER BY month) saves you from repeating the same window clause on every LAG call. Postgres, Snowflake, BigQuery, and DuckDB all support named windows. MySQL 8+ does too.

AOV doesn't get its own MoM column here on purpose. Percentage changes on a ratio metric are misleading — AOV can rise because you gained one whale while losing fifty small buyers. Better to look at revenue and order count separately and let the ratio speak for itself.

Don't compare a full month to a half month

The most common mistake in MoM reporting: comparing July's 31 days of revenue to whatever August has so far. Every month looks like a disaster until the 25th.

Fix it by only counting days both months share:

with day_cutoff as (
  select extract(day from current_date)::int as max_day
),
adjusted as (
  select
    date_trunc('month', created_at)::date as month,
    sum(total) as revenue,
    count(*)   as orders
  from orders
  cross join day_cutoff
  where extract(day from created_at) <= max_day
  group by 1
)
select
  a.month,
  a.revenue,
  prev.revenue as prev_revenue,
  round(100.0 * (a.revenue - prev.revenue)
          / nullif(prev.revenue, 0), 1) as mom_pct
from adjusted a
left join adjusted prev
  on prev.month = a.month - interval '1 month'
order by a.month;

If today is August 10th, this counts only days 1 through 10 of every month. July becomes July-1-through-10, making the comparison fair.

Worth noting the structure here, too. Aggregation in one CTE, comparisons in the outer query. I prefer this over stacking six nested LAG expressions in a single SELECT — it's easier to debug when the numbers look wrong, and they will, eventually. Put your heavy lifting in CTEs, shape the output at the end.

The business-day trap

Here's a gotcha that most SQL tutorials skip. February 2026 had 20 business days. March had 22. That's a 10% difference in selling time before anyone does anything differently.

If your revenue comes from B2B sales that mostly close on weekdays, comparing raw calendar months means comparing unequal time periods. Revenue-per-business-day is a better base:

with daily as (
  select
    created_at::date as day,
    date_trunc('month', created_at)::date as month,
    sum(total) as daily_rev
  from orders
  where extract(dow from created_at) between 1 and 5
  group by 1, 2
),
monthly_biz as (
  select
    month,
    sum(daily_rev) as revenue,
    count(*)       as biz_days,
    round(sum(daily_rev) / count(*), 2) as rev_per_biz_day
  from daily
  group by 1
)
select
  month,
  revenue,
  biz_days,
  rev_per_biz_day,
  lag(rev_per_biz_day) over (order by month) as prev_rpbd,
  round(
    100.0 * (rev_per_biz_day - lag(rev_per_biz_day) over (order by month))
      / nullif(lag(rev_per_biz_day) over (order by month), 0), 1
  ) as rpbd_mom_pct
from monthly_biz
order by month;

The MoM change on rev_per_biz_day tells you whether your actual sales velocity changed, not whether the calendar gave you more or fewer working days. For DTC and subscription businesses where revenue accrues seven days a week, this doesn't apply — stick with calendar months.

Putting this on a dashboard

For sparkline widgets — those small trend lines next to a KPI — the query is the simplest one in this post: the last 12 months of a metric, ordered by month. Twelve rows, one column. That's all a sparkline needs.

In Fastero, you can turn any of these queries into a number widget with a built-in trend arrow. The MoM percentage shows as a green or red delta, the sparkline renders behind the number, and the whole thing refreshes on a schedule you set. Wire the comparison table query to a table widget and you've got the entire Monday standup slide in one self-updating dashboard.

If window functions like LAG and PARTITION BY are still unfamiliar territory, that companion post covers the full set of patterns these queries depend on.

Try Fastero free — build MoM dashboards from live SQL with trend arrows and scheduled refreshes. No credit card required.