LAG

Returns a value from a previous row within the partition of a result set.

Syntax

LAG ( expr [, offset ])
   OVER (
         [ PARTITION BY partition_expr [, ...] ]
         [ ORDER BY order [ ASC | DESC ] [, ...]]
      )

offset ::= integer

Arguments

Parameter	Description
`expr`	Any value expression
`offset`	Specifies the offset of rows to scan back. If not specified, defaults to 1.

Returns

Same type as expr.

Notes

offset is evaluated with respect to the current row. If not specified, offset defaults to 1.
If there is no previous row (calculated against the current row), LAG returns NULL.

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   "Name" text(40),
   "Team" text(40),
   "Number" tinyint,
   "Position" text(2),
   "Age" tinyint,
   "Height" text(4),
   "Weight" real,
   "College" text(40),
   "Salary" float
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv
Name	Team	Number	Position	Age	Height	Weight	College	Salary
Avery Bradley	Boston Celtics	0.0	PG	25.0	6-2	180.0	Texas	7730337.0
Jae Crowder	Boston Celtics	99.0	SF	25.0	6-6	235.0	Marquette	6796117.0
John Holland	Boston Celtics	30.0	SG	27.0	6-5	205.0	Boston University
R.J. Hunter	Boston Celtics	28.0	SG	22.0	6-5	185.0	Georgia State	1148640.0
Jonas Jerebko	Boston Celtics	8.0	PF	29.0	6-10	231.0		5000000.0
Amir Johnson	Boston Celtics	90.0	PF	29.0	6-9	240.0		12000000.0
Jordan Mickey	Boston Celtics	55.0	PF	21.0	6-8	235.0	LSU	1170960.0
Kelly Olynyk	Boston Celtics	41.0	C	25.0	7-0	238.0	Gonzaga	2165160.0
Terry Rozier	Boston Celtics	12.0	PG	22.0	6-2	190.0	Louisville	1824360.0

Using `LAG` to access previous rows

Changed in version 2020.2: LAG and LEAD are supported from v2020.2.

This example calculates the salary between two players, starting from the highest salary.

t=> SELECT "Name",
.          "Salary",
.          LAG("Salary") OVER (ORDER BY "Salary" DESC) AS "Salary - previous",
.          LAG("Salary",1) OVER (ORDER BY "Salary" DESC) - "Salary" AS "Salary - diff"
.          -- LAG("Salary",1) is equivalent to LAG("Salary")
.   FROM   nba
.   LIMIT 11 ;
Name            | Salary   | Salary - previous | Salary - diff
----------------+----------+-------------------+--------------
Kobe Bryant     | 25000000 |                   |
LeBron James    | 22970500 |          25000000 |       2029500
Carmelo Anthony | 22875000 |          22970500 |         95500
Dwight Howard   | 22359364 |          22875000 |        515636
Chris Bosh      | 22192730 |          22359364 |        166634
Chris Paul      | 21468695 |          22192730 |        724035
Kevin Durant    | 20158622 |          21468695 |       1310073
Derrick Rose    | 20093064 |          20158622 |         65558
Dwyane Wade     | 20000000 |          20093064 |         93064
Brook Lopez     | 19689000 |          20000000 |        311000
DeAndre Jordan  | 19689000 |          19689000 |             0