SQL LAG(): Compare a Row With the Previous Row

LAG() lets a row look backwards: it returns a value from the previous row (or one several rows back) in the order you choose. It is the standard way to calculate change over time: month-over-month growth, the difference since the last reading, or whether a value went up or down.

The example table#

monthly_sales

Syntax#

LAG(column, offset, default) OVER (
  [PARTITION BY column]
  ORDER BY column
)

ORDER BY inside OVER is essential: "previous" only means something once rows are in order.

Previous month's revenue#

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month
FROM monthly_sales
WHERE region = 'North'
ORDER BY month;

month   | revenue | prev_month
--------+---------+-----------
2026-01 | 120     | NULL
2026-02 | 150     | 120
2026-03 | 110     | 150
2026-04 | 170     | 110

January has no previous month, so LAG returns NULL.

Change from the previous row#

Subtract the lagged value to get the change:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales
WHERE region = 'North'
ORDER BY month;

month   | revenue | change
--------+---------+-------
2026-01 | 120     | NULL
2026-02 | 150     | 30
2026-03 | 110     | -40
2026-04 | 170     | 60

Growth percentage#

SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_sales
WHERE region = 'North'
ORDER BY month;

month   | revenue | pct_change
--------+---------+-----------
2026-01 | 120     | NULL
2026-02 | 150     | 25
2026-03 | 110     | -26.7
2026-04 | 170     | 54.5

From 150 to 110 is a fall of 40, which is 26.7% of 150. If the previous value could be 0, wrap the divisor in NULLIF(..., 0) to avoid a division-by-zero error.

A default instead of NULL#

The third argument replaces the NULL on the first row:

SELECT month, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month
FROM monthly_sales
WHERE region = 'North'
ORDER BY month;

month   | revenue | prev_month
--------+---------+-----------
2026-01 | 120     | 0
2026-02 | 150     | 120
2026-03 | 110     | 150
2026-04 | 170     | 110

Use an offset of 12 with monthly data for a year-over-year comparison: LAG(revenue, 12).

Per group with PARTITION BY#

Without a partition, the first South row would "look back" at North's April. PARTITION BY region keeps each region separate:

SELECT region, month, revenue,
       LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev,
       revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS change
FROM monthly_sales
ORDER BY region, month;

region | month   | revenue | prev | change
-------+---------+---------+------+-------
North  | 2026-01 | 120     | NULL | NULL
North  | 2026-02 | 150     | 120  | 30
North  | 2026-03 | 110     | 150  | -40
North  | 2026-04 | 170     | 110  | 60
South  | 2026-01 | 90      | NULL | NULL
South  | 2026-02 | 90      | 90   | 0
South  | 2026-03 | 130     | 90   | 40
South  | 2026-04 | 125     | 130  | -5

Common mistakes#

Practice#

SELECT region, month, revenue
FROM (
  SELECT region, month, revenue,
         LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prv
  FROM monthly_sales
) t
WHERE revenue > prv
ORDER BY region, month;

region | month   | revenue
-------+---------+--------
North  | 2026-02 | 150
North  | 2026-04 | 170
South  | 2026-03 | 130

Key takeaway#