How does the compare to the previous row query work?

LAG(revenue) returns the revenue from the row before the current one in the ORDER BY order. The first row has no previous row, so LAG returns null there; the change is null too. NULLIF(..., 0) turns a zero denominator into null so the percentage does not raise a division-by-zero error.

What commonly goes wrong with compare to the previous row in SQL?

LAG compares with the previous row, not the previous month: a missing month makes it compare with two months back.

Compare to the Previous Row in SQL

Comparing with the previous row uses LAG to show how a value changed since the last period, such as revenue against last month.

Example query

This example assumes: monthly_revenue(month date, revenue numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       (revenue - LAG(revenue) OVER (ORDER BY month))
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS pct_change
FROM monthly_revenue
ORDER BY month;

How it works

LAG(revenue) returns the revenue from the row before the current one in the ORDER BY order.
The first row has no previous row, so LAG returns null there; the change is null too.
NULLIF(..., 0) turns a zero denominator into null so the percentage does not raise a division-by-zero error.

MySQL, BigQuery, SQL Server and SQLite differences

LAG and LEAD exist in PostgreSQL, MySQL 8+, SQL Server 2012+, BigQuery and SQLite 3.25+. LAG(x, 2) looks two rows back and LAG(x, 1, 0) supplies a default instead of null.

Common mistakes

LAG compares with the previous row, not the previous month: a missing month makes it compare with two months back.
Integer columns divide as integers in some databases; cast to numeric before dividing.
A change from a very small base produces a huge percentage; show the absolute change too.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show revenue each month and the change from the previous month.
In which months did revenue fall compared with the month before?