SQLite 3 reference · Window Functions
SQLite LAG() Function
Return a value from a previous row in the window, such as last month's total, or a default when there isn't one.
SQLite 3 Since 3.25.0
Syntax
lag(X, offset, default) OVER (PARTITION BY ... ORDER BY ...)
Parameters
| Parameter | Description |
|---|---|
X |
A column or expression. |
offset optional |
How many rows back. Default: 1 |
default optional |
The value when there's no such row. Default: NULL |
Return value
The value from that row.
Examples
SELECT order_id, order_date,
lag(order_date) OVER (ORDER BY order_date) AS previous_order
FROM orders
LIMIT 4;
Output
order_id | order_date | previous_order 1 | 2026-01-05 | NULL 2 | 2026-01-12 | 2026-01-05 3 | 2026-02-02 | 2026-01-12 4 | 2026-02-14 | 2026-02-02
SELECT customer_id, order_date,
julianday(order_date) - julianday(lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_last
FROM orders
WHERE customer_id = 1;
Output
customer_id | order_date | days_since_last 1 | 2026-01-05 | NULL 1 | 2026-02-02 | 28 1 | 2026-04-22 | 79
Version history
| Version | Change |
|---|---|
| SQLite 3.25.0 | Added |
In other languages
LAG()· PostgreSQL
Related
LEAD()· Return a value from a following row in the window, such as the next order's date, or a default when there isn't one.FIRST_VALUE()· Return a value from the first row of the window frame, such as each department's highest salary.- SQL Window Functions · tutorial