English words and abbreviations
lag = fall behind
What is it for?
Compare adjacent values or the first and last in a period.
Return valueType depends on input
Syntax
LAG(value[, offset[, fallback]]) OVER ([PARTITION BY partition] ORDER BY order)
What goes in each argument?
| Argument | Type / notation | Example input | Meaning / default |
|---|
valueRequired | EXPRESSION | value / 42 / NULL | Value or column, with a compatible type. |
offsetOptional | INTEGER | 1 / 0 / -1 | Positive: preceding rows. +1 is previous, −1 is next. 0 is the current row; default 1. Direction follows ORDER BY inside OVER. |
fallbackOptional | EXPRESSION | -1 / NULL | Returned when the target row is outside the partition; default NULL. An existing row whose value is NULL still returns NULL. |
partitionOptional | EXPRESSION | department / user_id | Grouping column in PARTITION BY. Omitted: all rows form one partition. |
orderRequired | ORDER BY EXPRESSION | value DESC / id | Ordering expression in OVER or an aggregate clause, not a function argument. |
SQL you can run as written
Input data is included. No tables to create.
WITH data AS (
SELECT 1 AS id, 10 AS value
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, 30
)
SELECT id, value,
LAG(value, 1, -1) OVER (ORDER BY id) AS result
FROM data
ORDER BY id;
Result of this SQL
| id | value | result |
|---|
1 | 10 | -1 |
2 | NULL | 10 |
3 | 30 | NULL |
Before you use it
Previous/next follow ORDER BY inside OVER: with dates ascending, next is newer; descending, next is older. Add a unique ID to break ties. References stay in the partition and count rows with NULL values. Snowflake reverses direction for negative offsets. Default RESPECT NULLS counts NULL rows; IGNORE NULLS skips them when counting. fallback applies only when no target row exists.
In the other dialect BigQuery
LAG(value[, offset[, fallback]]) OVER ([PARTITION BY partition] ORDER BY order)
BigQuery offset must be a nonnegative integer literal or parameter; negative/NULL is an error. fallback is a compatible constant expression, used only when the target row is absent.
Official reference ↗