LAG

Returns the value from a following row at the specified offset from the current value. Offsets start at 1. The offset can be any positive integer. If the offset is null or greater than the number of values in the window, a default value is returned, or null if unspecified.

Parameter	Type	Description
`value`	any	The name of the column to evaluate
`offset`	integer	The number of rows to offset
`default value`	any	The value to put if there are no more rows that meet the partition requirement

Returns: any

Example

query:

SELECT account, create_date, order_value, LAG(order_value, 1, -1) OVER (PARTITION BY account ORDER BY create_date)
  FROM jun_2017_orders

siyeh/sql-crm-example-data Run query Copy code

result:

account	create_date	order_value	lag
Betasoloin	2017-06-03	5100	-1
Betasoloin	2017-06-04	2887	5100
Betasoloin	2017-06-06	4492	2887
Betasoloin	2017-06-09	5182	4492
Betasoloin	2017-06-24	1036	5182
Gekko & Co	2017-06-02	5304	-1
Gekko & Co	2017-06-11	1075	5304