SQL Query to Calculate Month Over Month Growth

Month over month growth is one of the most fundamental business metrics in any data-driven organization. Whether you are tracking revenue, active users, orders, or any other key metric — knowing how it changed compared to last month tells you whether things are improving, declining, or staying flat.

Calculating it manually is simple. Calculating it at scale across dozens of metrics, regions, or product lines automatically, in a single SQL query is where things get interesting.

In this guide, we will walk through exactly how to calculate month over month growth in SQL using LAG, CTEs, and window functions from the simplest single-metric case to multi-dimensional growth analysis across multiple segments.

What Is Month Over Month Growth?

Month over month (MoM) growth measures the percentage change in a metric from one month to the next.

The Formula

MoM Growth % = ((Current Month Value - Previous Month Value) / Previous Month Value) × 100

Example:

• January Revenue: $120,000
• February Revenue: $138,000

MoM Growth = ((138,000 - 120,000) / 120,000) × 100 = 15%

February grew 15% over January.

Why This Matters

MoM growth is preferred over absolute values because it normalizes for scale — a $18,000 increase means something very different for a $120,000 business versus a $1,200,000 business. Growth percentage provides context.

It is also more actionable than annual comparisons — monthly growth lets you detect trends faster and respond to problems or opportunities in near real time.

Setting Up the Dataset

sql

CREATE TABLE monthly_revenue (
    month_date  DATE,
    region      VARCHAR(50),
    product     VARCHAR(50),
    revenue     DECIMAL(12, 2),
    orders      INT,
    new_customers INT
);

INSERT INTO monthly_revenue VALUES
('2024-01-01', 'North', 'Electronics', 125000, 520, 180),
('2024-02-01', 'North', 'Electronics', 138000, 580, 195),
('2024-03-01', 'North', 'Electronics', 142000, 610, 210),
('2024-04-01', 'North', 'Electronics', 131000, 545, 165),
('2024-05-01', 'North', 'Electronics', 155000, 650, 225),
('2024-06-01', 'North', 'Electronics', 162000, 680, 240),
('2024-01-01', 'South', 'Electronics', 98000,  410, 140),
('2024-02-01', 'South', 'Electronics', 104000, 435, 148),
('2024-03-01', 'South', 'Electronics', 97000,  405, 132),
('2024-04-01', 'South', 'Electronics', 112000, 468, 158),
('2024-05-01', 'South', 'Electronics', 119000, 497, 170),
('2024-06-01', 'South', 'Electronics', 128000, 535, 185),
('2024-01-01', 'North', 'Furniture',   45000,  180, 65),
('2024-02-01', 'North', 'Furniture',   48000,  195, 70),
('2024-03-01', 'North', 'Furniture',   52000,  210, 78),
('2024-04-01', 'North', 'Furniture',   49000,  198, 68),
('2024-05-01', 'North', 'Furniture',   58000,  235, 88),
('2024-06-01', 'North', 'Furniture',   61000,  248, 92);

Method 1: Basic MoM Growth Using LAG

The LAG function is the cleanest and most efficient way to calculate month over month growth. It retrieves the previous row’s value without any self-join.

Single Region, Single Metric

sql

SELECT
    month_date,
    region,
    revenue,
    LAG(revenue) OVER (ORDER BY month_date) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month_date) AS revenue_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month_date))
        * 100.0
        / NULLIF(LAG(revenue) OVER (ORDER BY month_date), 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
WHERE region = 'North'
AND product = 'Electronics'
ORDER BY month_date;

Result:

month_date	region	revenue	prev_month_revenue	revenue_change	mom_growth_pct
2024-01-01	North	125000	NULL	NULL	NULL
2024-02-01	North	138000	125000	13000	10.40
2024-03-01	North	142000	138000	4000	2.90
2024-04-01	North	131000	142000	-11000	-7.75
2024-05-01	North	155000	131000	24000	18.32
2024-06-01	North	162000	155000	7000	4.52

January returns NULL — there is no previous month to compare against. April shows a negative 7.75% — revenue declined that month.

Why NULLIF Is Important

The formula uses NULLIF(prev_value, 0) to prevent division by zero errors. If the previous month’s value is zero, dividing by it causes an error. NULLIF converts 0 to NULL and dividing by NULL returns NULL rather than crashing.

sql

-- Without NULLIF — crashes if prev_month_revenue = 0
(revenue - prev_revenue) * 100.0 / prev_revenue

-- With NULLIF — safe division
(revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0)

Method 2: MoM Growth With PARTITION BY — Multiple Segments

When your data contains multiple regions, products, or categories, you must use PARTITION BY to calculate MoM growth independently within each group.

Without PARTITION BY, LAG compares the last row of North to the first row of South — producing completely wrong cross-segment comparisons.

sql

SELECT
    month_date,
    region,
    product,
    revenue,
    LAG(revenue) OVER (
        PARTITION BY region, product
        ORDER BY month_date
    ) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        )) * 100.0
        / NULLIF(LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ), 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY region, product, month_date;

Result (sample):

month_date	region	product	revenue	prev_revenue	mom_growth_pct
2024-01-01	North	Electronics	125000	NULL	NULL
2024-02-01	North	Electronics	138000	125000	10.40
2024-03-01	North	Electronics	142000	138000	2.90
2024-04-01	North	Electronics	131000	142000	-7.75
2024-01-01	North	Furniture	45000	NULL	NULL
2024-02-01	North	Furniture	48000	45000	6.67
2024-01-01	South	Electronics	98000	NULL	NULL
2024-02-01	South	Electronics	104000	98000	6.12
2024-03-01	South	Electronics	97000	104000	-6.73

Each region-product combination now has its own independent MoM calculation — North Electronics starts fresh in January and South Electronics starts fresh in January without cross-contamination.

Method 3: Using a CTE for Cleaner Queries

Repeating the LAG expression three times (for the raw value, the change, and the percentage) is verbose and error-prone. Use a CTE to calculate LAG once and reference it multiple times.

sql

WITH monthly_with_lag AS (
    SELECT
        month_date,
        region,
        product,
        revenue,
        orders,
        new_customers,
        LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ) AS prev_revenue,
        LAG(orders) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ) AS prev_orders,
        LAG(new_customers) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ) AS prev_new_customers
    FROM monthly_revenue
)
SELECT
    month_date,
    region,
    product,
    revenue,
    prev_revenue,
    revenue - prev_revenue AS revenue_change,
    ROUND(
        (revenue - prev_revenue) * 100.0
        / NULLIF(prev_revenue, 0),
        2
    ) AS revenue_mom_pct,
    orders - prev_orders AS orders_change,
    ROUND(
        (orders - prev_orders) * 100.0
        / NULLIF(prev_orders, 0),
        2
    ) AS orders_mom_pct,
    ROUND(
        (new_customers - prev_new_customers) * 100.0
        / NULLIF(prev_new_customers, 0),
        2
    ) AS new_customers_mom_pct
FROM monthly_with_lag
ORDER BY region, product, month_date;

Result:

month_date	region	product	revenue	revenue_mom_pct	orders_mom_pct	new_customers_mom_pct
2024-01-01	North	Electronics	125000	NULL	NULL	NULL
2024-02-01	North	Electronics	138000	10.40	11.54	8.33
2024-03-01	North	Electronics	142000	2.90	5.17	7.69
2024-04-01	North	Electronics	131000	-7.75	-10.66	-21.43

Now you have MoM growth for revenue, orders, and new customers in one clean query with LAG calculated only once per metric.

Method 4: Self-Join Approach (For Older SQL Versions)

If you are on an older SQL database that does not support window functions, a self-join achieves the same result — though it is slower and harder to read.

sql

SELECT
    curr.month_date,
    curr.region,
    curr.revenue AS current_revenue,
    prev.revenue AS prev_revenue,
    curr.revenue - prev.revenue AS revenue_change,
    ROUND(
        (curr.revenue - prev.revenue) * 100.0
        / NULLIF(prev.revenue, 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
    ON curr.region = prev.region
    AND curr.product = prev.product
    AND prev.month_date = DATE_ADD(curr.month_date, INTERVAL -1 MONTH)
WHERE curr.product = 'Electronics'
ORDER BY curr.region, curr.month_date;

The self-join matches each month to its previous month by joining on month_date - 1 MONTH. The LEFT JOIN ensures the first month of each region appears even though it has no previous month match.

Note on DATE_ADD syntax:

sql

-- MySQL
DATE_ADD(curr.month_date, INTERVAL -1 MONTH)

-- PostgreSQL
curr.month_date - INTERVAL '1 month'

-- SQL Server
DATEADD(MONTH, -1, curr.month_date)

-- BigQuery / Snowflake
DATE_SUB(curr.month_date, INTERVAL 1 MONTH)

Method 5: Aggregating Raw Data First, Then Calculating Growth

In most real systems, you do not have a pre-aggregated monthly table. You start with raw transactional data and need to aggregate by month before calculating growth.

sql

-- Simulating raw orders table
WITH raw_orders AS (
    SELECT '2024-01-15' AS order_date, 'North' AS region, 1500.00 AS amount
    UNION ALL SELECT '2024-01-22', 'North', 2200.00
    UNION ALL SELECT '2024-02-05', 'North', 1800.00
    UNION ALL SELECT '2024-02-18', 'North', 3100.00
    UNION ALL SELECT '2024-03-10', 'North', 2500.00
    -- In practice: your actual orders/transactions table
),

-- Step 1: Aggregate raw data to monthly totals
monthly_totals AS (
    SELECT
        DATE_TRUNC('month', CAST(order_date AS DATE)) AS month_date,
        region,
        SUM(amount) AS total_revenue,
        COUNT(*) AS order_count
    FROM raw_orders
    GROUP BY
        DATE_TRUNC('month', CAST(order_date AS DATE)),
        region
),

-- Step 2: Add LAG for previous month values
monthly_with_prev AS (
    SELECT
        month_date,
        region,
        total_revenue,
        order_count,
        LAG(total_revenue) OVER (
            PARTITION BY region
            ORDER BY month_date
        ) AS prev_revenue,
        LAG(order_count) OVER (
            PARTITION BY region
            ORDER BY month_date
        ) AS prev_order_count
    FROM monthly_totals
)

-- Step 3: Calculate MoM growth
SELECT
    month_date,
    region,
    total_revenue,
    prev_revenue,
    ROUND(
        (total_revenue - prev_revenue) * 100.0
        / NULLIF(prev_revenue, 0),
        2
    ) AS revenue_mom_pct,
    order_count,
    ROUND(
        (order_count - prev_order_count) * 100.0
        / NULLIF(prev_order_count, 0),
        2
    ) AS orders_mom_pct
FROM monthly_with_prev
ORDER BY region, month_date;

This three-step CTE pattern — aggregate first, add LAG second, calculate growth third — is the most production-ready approach for real-world data pipelines.

Method 6: MoM Growth With Running Totals and Cumulative Context

Add cumulative revenue and the growth trend to provide richer context alongside MoM growth.

sql

WITH monthly_with_lag AS (
    SELECT
        month_date,
        region,
        revenue,
        LAG(revenue) OVER (
            PARTITION BY region
            ORDER BY month_date
        ) AS prev_revenue
    FROM monthly_revenue
    WHERE product = 'Electronics'
)
SELECT
    month_date,
    region,
    revenue,
    prev_revenue,
    ROUND(
        (revenue - prev_revenue) * 100.0
        / NULLIF(prev_revenue, 0),
        2
    ) AS mom_growth_pct,
    SUM(revenue) OVER (
        PARTITION BY region
        ORDER BY month_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(ROUND(
        (revenue - prev_revenue) * 100.0
        / NULLIF(prev_revenue, 0),
        2
    )) OVER (
        PARTITION BY region
        ORDER BY month_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3mo_avg_growth
FROM monthly_with_lag
ORDER BY region, month_date;

Result (North Electronics):

month_date	revenue	mom_growth_pct	cumulative_revenue	rolling_3mo_avg_growth
2024-01-01	125000	NULL	125000	NULL
2024-02-01	138000	10.40	263000	NULL
2024-03-01	142000	2.90	405000	6.65
2024-04-01	131000	-7.75	536000	1.85
2024-05-01	155000	18.32	691000	4.49
2024-06-01	162000	4.52	853000	5.03

The rolling 3-month average growth smooths out volatile individual months — April’s -7.75% spike looks less alarming when you see the 3-month average is still positive at 1.85%.

Method 7: Flagging Growth Thresholds

Add a classification column that categorizes each month’s growth for easy filtering and alerting.

sql

WITH monthly_with_lag AS (
    SELECT
        month_date,
        region,
        product,
        revenue,
        LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ) AS prev_revenue
    FROM monthly_revenue
),
growth_calculated AS (
    SELECT
        month_date,
        region,
        product,
        revenue,
        prev_revenue,
        ROUND(
            (revenue - prev_revenue) * 100.0
            / NULLIF(prev_revenue, 0),
            2
        ) AS mom_growth_pct
    FROM monthly_with_lag
    WHERE prev_revenue IS NOT NULL
)
SELECT
    month_date,
    region,
    product,
    revenue,
    mom_growth_pct,
    CASE
        WHEN mom_growth_pct >= 15   THEN 'Strong Growth'
        WHEN mom_growth_pct >= 5    THEN 'Moderate Growth'
        WHEN mom_growth_pct >= 0    THEN 'Flat / Slight Growth'
        WHEN mom_growth_pct >= -10  THEN 'Moderate Decline'
        ELSE                             'Significant Decline'
    END AS growth_classification
FROM growth_calculated
ORDER BY region, product, month_date;

Result (North Electronics):

month_date	revenue	mom_growth_pct	growth_classification
2024-02-01	138000	10.40	Moderate Growth
2024-03-01	142000	2.90	Flat / Slight Growth
2024-04-01	131000	-7.75	Moderate Decline
2024-05-01	155000	18.32	Strong Growth
2024-06-01	162000	4.52	Flat / Slight Growth

This classification is immediately useful for business reporting — stakeholders can filter for “Significant Decline” months to investigate problems or “Strong Growth” months to understand what drove performance.

Method 8: Year Over Year Growth Using the Same Pattern

The same LAG pattern applies for year over year (YoY) growth — just change the offset from 1 to 12.

sql

WITH monthly_with_lag AS (
    SELECT
        month_date,
        region,
        product,
        revenue,
        LAG(revenue, 12) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ) AS same_month_last_year
    FROM monthly_revenue
)
SELECT
    month_date,
    region,
    product,
    revenue,
    same_month_last_year,
    ROUND(
        (revenue - same_month_last_year) * 100.0
        / NULLIF(same_month_last_year, 0),
        2
    ) AS yoy_growth_pct
FROM monthly_with_lag
WHERE same_month_last_year IS NOT NULL
ORDER BY region, product, month_date;

LAG(revenue, 12) looks back 12 rows — comparing January 2024 to January 2023, February 2024 to February 2023, and so on. This automatically handles seasonality because you are comparing the same month in different years.

Comparing MoM Growth Across Regions in One View

Pivot MoM growth rates across regions to compare them side by side in a single result.

sql

WITH monthly_with_lag AS (
    SELECT
        month_date,
        region,
        revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (
                PARTITION BY region
                ORDER BY month_date
            )) * 100.0
            / NULLIF(LAG(revenue) OVER (
                PARTITION BY region
                ORDER BY month_date
            ), 0),
            2
        ) AS mom_growth_pct
    FROM monthly_revenue
    WHERE product = 'Electronics'
)
SELECT
    month_date,
    MAX(CASE WHEN region = 'North' THEN mom_growth_pct END) AS north_growth_pct,
    MAX(CASE WHEN region = 'South' THEN mom_growth_pct END) AS south_growth_pct
FROM monthly_with_lag
WHERE mom_growth_pct IS NOT NULL
GROUP BY month_date
ORDER BY month_date;

Result:

month_date	north_growth_pct	south_growth_pct
2024-02-01	10.40	6.12
2024-03-01	2.90	-6.73
2024-04-01	-7.75	15.46
2024-05-01	18.32	6.25
2024-06-01	4.52	7.56

March was negative for South (-6.73%) while North grew 2.9%. April was negative for North (-7.75%) while South grew 15.46%. These regional differences are completely invisible in aggregate totals — the pivot view makes them immediately actionable.

MoM Calculation Approaches

Method	Handles Multiple Segments	Handles Missing Months	Complexity	Best For
LAG (basic)	No PARTITION BY	No	Easy	Single series, quick analysis
LAG + PARTITION BY	Yes	No	Easy	Multiple segments
CTE + LAG	Yes	No	Easy	Multiple metrics, clean code
Self-join	Yes	Yes	Moderate	Older SQL databases
Aggregate + LAG	Yes	No	Moderate	Raw transactional data
LAG + Running totals	Yes	No	Moderate	Executive reporting
LAG offset 12	Yes	No	Easy	Year over year comparison

Handling Missing Months

A common real-world problem — what if a month has no data? LAG will skip that month and compare to the wrong previous period.

sql

-- Generate a complete month spine first to handle gaps
WITH month_spine AS (
    SELECT DATE_TRUNC('month', generate_series(
        '2024-01-01'::DATE,
        '2024-06-01'::DATE,
        '1 month'::INTERVAL
    )) AS month_date
    -- PostgreSQL syntax for generating date series
    -- MySQL: use a calendar table or recursive CTE
    -- SQL Server: use a numbers table with DATEADD
),
region_spine AS (
    SELECT DISTINCT region, product FROM monthly_revenue
),
complete_spine AS (
    SELECT ms.month_date, rs.region, rs.product
    FROM month_spine ms
    CROSS JOIN region_spine rs
),
filled_data AS (
    SELECT
        cs.month_date,
        cs.region,
        cs.product,
        COALESCE(mr.revenue, 0) AS revenue
    FROM complete_spine cs
    LEFT JOIN monthly_revenue mr
        ON cs.month_date = mr.month_date
        AND cs.region = mr.region
        AND cs.product = mr.product
)
SELECT
    month_date,
    region,
    product,
    revenue,
    LAG(revenue) OVER (
        PARTITION BY region, product
        ORDER BY month_date
    ) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        )) * 100.0
        / NULLIF(LAG(revenue) OVER (
            PARTITION BY region, product
            ORDER BY month_date
        ), 0),
        2
    ) AS mom_growth_pct
FROM filled_data
ORDER BY region, product, month_date;

The month spine ensures every month appears in the data — even months with zero activity — so LAG always compares to the correct adjacent month.

Real-World Use Cases

SaaS Revenue Reporting — Monthly recurring revenue (MRR) growth tracked by plan tier (Basic, Pro, Enterprise). PARTITION BY plan tier ensures each tier’s growth is calculated independently — revealing that Enterprise grew 25% while Basic declined 3% in the same month.

E-commerce Performance — GMV (Gross Merchandise Value) growth by product category and region. A retail analytics team uses the threshold classification to automatically flag categories with greater than -10% decline for weekly review meetings.

User Growth Analysis — Monthly active users, new signups, and churned users tracked with MoM growth. The three-month rolling average smooths seasonality effects when presenting to investors.

Marketing Attribution — Spend and revenue growth by acquisition channel. When paid search spend grew 40% MoM but revenue from that channel grew only 8% MoM, the efficiency decline became immediately visible — triggering a campaign audit.

Common Mistakes to Avoid

• Not using PARTITION BY with multiple segments — Without PARTITION BY, LAG compares the last row of one region to the first row of the next region — producing completely meaningless cross-segment comparisons that look like real data
• Dividing by zero without NULLIF — If any previous month value is zero, the division crashes. Always wrap the denominator: NULLIF(prev_revenue, 0)
• Confusing NULL and zero in growth results — NULL means no previous period exists (first month). Zero percent means no change. These are completely different and must not be treated the same in downstream reporting
• Not handling missing months before applying LAG — If April has no data, LAG in May compares to March — a two-month comparison labeled as one-month growth. Always generate a complete date spine before applying LAG
• Using simple multiplication to annualize MoM growth — A 3% monthly growth rate is NOT 36% annually. The correct annualization is (1 + 0.03)^12 - 1 = 42.6%. Always use the compounding formula for annualization
• Mixing aggregation levels — Calculating MoM growth on already-aggregated data that includes different time periods or data completeness levels produces inconsistent results. Ensure your input data is complete and consistently aggregated before applying LAG

Quick Reference Cheat Sheet

Task	SQL Pattern
Basic MoM growth	(revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0)
MoM within segments	Add PARTITION BY region, product inside OVER clause
Multiple metrics cleanly	Use CTE — calculate LAG once, reference multiple times
From raw transactions	CTE 1: aggregate to monthly → CTE 2: add LAG → CTE 3: calculate growth
Year over year	LAG(revenue, 12) OVER (PARTITION BY region ORDER BY month)
3-month rolling average	AVG(mom_pct) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Threshold classification	CASE WHEN mom_pct >= 15 THEN 'Strong' WHEN ... END
Handle missing months	Generate month spine → LEFT JOIN data → fill with COALESCE

Calculating month over month growth in SQL is one of the most practical and frequently needed analytical queries in any data-driven organization. Once you have the LAG pattern down, it extends naturally to multiple segments, multiple metrics, year over year comparisons, and rolling averages.

Here is a quick recap of everything we covered:

• MoM growth = (current – previous) / previous × 100 — always use NULLIF to handle division by zero
• LAG with PARTITION BY is the cleanest approach for multi-segment growth analysis
• Use a CTE to calculate LAG once and reference it multiple times for multiple metrics
• Aggregate raw transaction data first, then apply LAG in a subsequent CTE
• Use LAG with offset 12 for year over year growth using the same pattern
• Generate a complete month spine to handle missing months correctly before applying LAG
• Add CASE WHEN classification to make growth thresholds actionable in reports

Start with the basic LAG query for a single metric, add PARTITION BY when you have multiple segments, and build up to the full multi-metric CTE pattern as your reporting needs grow.

FAQs