SQLearnAcademy
←Function reference
WindowSQL standard

LAG

Takes a value from the previous row to compare with the current one.

Syntax

LAG(<expression>) OVER (ORDER BY <sort key>)
LAG(<expression>, <offset>, <default>) OVER (ORDER BY <sort key>)
LAG(<expression>) OVER (PARTITION BY <group> ORDER BY <sort key>)

Looks at the row above in the given order and returns a value from it. The offset is how many rows back (1 by default), and the third argument is used when there's no previous row. Its mirror function, LEAD, takes a value from the next row.

Data for the examples

orders

idcreated_atamount
12026-07-03 10:15:00120.50
22026-07-28 18:40:0080.25
32026-08-09 09:05:00199.99
42026-08-21 14:30:0045.00
52026-09-02 11:00:0064.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The amount of the previous order
SELECT id, created_at, amount,
LAG(amount) OVER (ORDER BY created_at) AS previous_amount
FROM orders;
idcreated_atamountprevious_amount
12026-07-03 10:15:00120.50NULL
22026-07-28 18:40:0080.25120.50
32026-08-09 09:05:00199.9980.25
42026-08-21 14:30:0045.00199.99
52026-09-02 11:00:0064.7545.00

The first order has no previous one, so there's NULL there.

How much the amount changed
SELECT id, amount,
amount - LAG(amount) OVER (ORDER BY created_at) AS difference
FROM orders;
idamountdifference
1120.50NULL
280.25-40.25
3199.99119.74
445.00-154.99
564.7519.75
Previous and next side by side
SELECT id, amount,
LAG(amount) OVER (ORDER BY created_at) AS previous_amount,
LEAD(amount) OVER (ORDER BY created_at) AS next_amount
FROM orders;
idamountprevious_amountnext_amount
1120.50NULL80.25
280.25120.50199.99
3199.9980.2545.00
445.00199.9964.75
564.7545.00NULL

Watch out

What counts as “previous” is decided by the ORDER BY in OVER. Without it the order is unpredictable — always set it explicitly.

For the first row the result is NULL, so any difference with it is NULL too. If you need another value, pass the third argument: LAG(amount, 1, 0).

You can't write LAG in WHERE: you'll get window functions are not allowed in WHERE. Calculate it in a subquery first and filter outside.

Related functions

LEADFIRST_VALUEROW_NUMBER