Lead/Lag analytic functions

Lead/Lag functions help to get data from the next/previous row in a query.

Syntax for these functions is:

Lead (expr [, offset, default-val ] ) over (order by expr)

Lag (expr [, offset, default-val ] ) over (order by expr)

Expr can be anything except another analytic function i.e. these functions cannot be nested.

Offset is the number of rows looked ahead or behind. Default is 1

Default-val is returned from these functions, if there is no row at the given offset from current row. Default is null.

Example:

The above functions can come very handy when we want to have a difference or sum or average etc between two consecutive rows in a query.