By Prakhar Shrivastava · October 12, 2026

No. LAG returns a value from an earlier row in the ordered partition. If February is absent, the row before March may be January. Check that the preceding period is the expected calendar month before calculating month-over-month growth.

A plausible number with the wrong label

Consider a fictional monthly export. US revenue is USD 100 in January and USD 150 in March, with no February record. UK revenue is USD 200, USD 0, and USD 100 across January through March. These are synthetic reporting-currency amounts, not local-currency comparisons or exchange-rate assumptions.

A simple LAG calculation reports a 50% increase for US March because it compares 150 with 100. That is a January-to-March comparison. It is not evidence of 50% month-over-month growth. The missing February record might represent no sales, an incomplete import, or an excluded business unit. The query cannot decide which explanation is true.

Test both the previous value and its period

This compact example uses month numbers 1–3 within a single year. Each market has at most one record per month. It deliberately retains the prior month number so the comparison can be audited. Run the query in SQLite; the result was checked with SQLite 3.50.4.

WITH monthly(market,month_no,revenue_usd) AS (VALUES
('US',1,100),('US',3,150),('UK',1,200),('UK',2,0),('UK',3,100)),
lagged AS (SELECT *, LAG(month_no) OVER(PARTITION BY market ORDER BY month_no) AS previous_month,
LAG(revenue_usd) OVER(PARTITION BY market ORDER BY month_no) AS previous_revenue FROM monthly)
SELECT market,month_no,previous_month,previous_revenue,
CASE WHEN previous_month=month_no-1 AND previous_revenue<>0
THEN 100.0*(revenue_usd-previous_revenue)/previous_revenue END AS growth_pct
FROM lagged ORDER BY market,month_no

What should the result show?

• UK January: no previous record, so growth is NULL.
• UK February: January is adjacent and has USD 200 revenue. The change to zero is −100%.
• UK March: February exists, but its revenue is zero. Percentage growth is undefined; keep it NULL rather than reporting zero percent.
• US January: no previous record.
• US March: the preceding row is January, so the adjacency check prevents an incorrect month-over-month percentage.

The two automated result checks validate the complete five-row output and confirm that the unguarded US calculation would return 50. They cover two different failures: a missing comparison period and a zero denominator. A NULL result should be accompanied by a reason in a production report, such as missing prior period or zero prior revenue.

How should this work across years?

Do not use bare month-of-year numbers in a multi-year report. Use a calendar table with a unique, consecutive month index spanning years, or a month-start date with your database’s date arithmetic. The previous index must equal the current index minus one. Validate that each market and period has one row before applying LAG; duplicate monthly rows make the previous-row relationship ambiguous.

Partition by the full identity of the series. If revenue is separated by market and product, include both keys. Otherwise the previous row may belong to a different product. Aggregate transaction rows to the intended monthly grain before applying this calculation.

Should the missing month be filled with zero?

Only when the source is known to be complete and absence means no activity. Build a calendar with every eligible market-month and retain a completeness indicator. A verified no-sales month can have zero revenue. An unreceived or unfinished period should remain unknown. Filling every missing month with zero would manufacture a decline and then a zero-denominator rebound.

Also retain enough history before filtering the output. If you remove February before computing LAG, March cannot see it. Compute the comparison over the required input period, then apply the final display filter outside that calculation. Compare complete reporting periods and state the timezone and cutoff used to assign transactions to months.

Related practice and sources

Learn the syntax in the window-function reference, build a calendar in the missing-date moving-average exercise, and distinguish NULL from zero. For decimal arithmetic, review integer division in rates.

The official PostgreSQL window-function reference defines LAG in terms of rows within an ordered partition. Date arithmetic varies by database; adapt that part explicitly rather than assuming a row offset is a calendar interval.