LAG OVER

Returns the value of the input expression at the given offset before the current row within the requested window. For more information on usage, please refer to Window Functions.

Syntax

LAG ( <expression> [, <offset> [, <default> ]] )
    OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ { ASC | DESC } ] )

Parameters

Parameter	Description	Supported input types
`<expression>`	Any valid expression that will be returned based on the `<offset>.`	Any
`<offset>`	The number of rows backward from the current row from which to obtain a value. A negative number will act as LEAD(). The offset must be a literal `INTEGER`.	`INTEGER`
`<default>`	The expression to return when the offset goes out of the bounds of the window. Must be a literal of the same type as `<expression>`. The default is `NULL`.	Any
`<partition_by>`	The expression used for the `PARTITION BY` clause.	Any
`<order_by>`	An expression used for the `ORDER BY` clause.	Any

Example

In the example below, LAG returns the nickname of the player one position behind (player_before) and one position ahead (player_after) in alphabetical order within each level. NULL is returned when no row exists at the given offset.

SELECT
    nickname,
    level,
    LAG(nickname, 1) OVER (PARTITION BY level ORDER BY nickname) AS player_before,
    LAG(nickname, -1) OVER (PARTITION BY level ORDER BY nickname) AS player_after
FROM
    (VALUES
        ('kennethpark', 9),
        ('rileyjon', 9),
        ('sabrina21', 9),
        ('ymatthews', 9)
    ) AS t(nickname, level);

nickname text	level int	player_before text null	player_after text null
kennethpark	9	NULL	rileyjon
rileyjon	9	kennethpark	sabrina21
sabrina21	9	rileyjon	ymatthews
ymatthews	9	sabrina21	NULL

Rows: 4Execution time: 6.89ms

FIRST_VALUE

LEAD OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Example

​Syntax

​Parameters

​Example

Syntax

Parameters

Example