LAG:

Using this function we can fetch the nth row before (Lag) the Current row without using self-join.

It is useful when we need to compare the current row values with values in a previous row.

Syntax:

LAG (scalar_expression [,offset] [,default])

    OVER ( [ partition_by_clause ] order_by_clause )

Lets explore it. Before exploring let us first create a table and insert some records.

Create an Employee table and insert some records to it.

EXAMPLE: Now my requirement is to show what the previous Salary before current hike was?

Solution Using Lag function:

lag1 lag2

LEAD:

Using this function we can fetch the nth row after (LEAD) the current row without using self-join.

It is useful when we need to compare the current row values with values in a following row.

Syntax:

LEAD (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

EXAMPLE: My requirement is to show what my following year salary is?

lead1 lead2

Note: Default offset value is 1. We can specify offset value by a column or a sub query, or other expression that evaluates to a positive integer. Negative value or analytical function can’t be used as offset value