PostgreSQL 18 reference · Window Functions

PostgreSQL LAG() Function

Return a value from a previous row in the window, or a default when there isn't one. Ideal for comparing with the last period.

PostgreSQL 18 Since 8.4

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

lag(value, offset, default) OVER (PARTITION BY ... ORDER BY ...)

Parameters

Parameter	Description
`value`	The expression to read.
`offset` optional	Rows back. Default: `1`
`default` optional	Used when there's no such row. Default: `NULL`

Return value

The value from that row.

Examples

Days since the previous order

SELECT order_id, order_date, order_date - lag(order_date) OVER (ORDER BY order_date) AS days_since_previous
FROM orders
LIMIT 4;

Output

 order_id | order_date | days_since_previous
----------+------------+---------------------
        1 | 2026-01-05 |                NULL
        2 | 2026-01-12 |                   7
        3 | 2026-02-02 |                  21
        4 | 2026-02-14 |                  12
(4 rows)

Run this on your computer — see “Get Started” for setup.

Month-over-month change

SELECT month, orders, orders - lag(orders, 1, 0) OVER (ORDER BY month) AS change
FROM (SELECT date_trunc('month', order_date)::date AS month, count(*) AS orders FROM orders GROUP BY 1) AS m
ORDER BY month
LIMIT 4;