Lag and lead family syntax

The syntax for the lag and lead family is as follows:

{lag | lead}
      (<value expression>, [<offset> [, <default>]]) OVER
      ([partition by <value_expression>[,...]]
      ORDER BY <value_expression> [asc | desc] [nulls {first | last}] 
[,...])

lag

Provides access to more than one row of a table at the same time without a self-join. The lag function provides access to a row at a physical offset before that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

lead

Provides access to more than one row of a table at the same time without a self-join. The lead function provides access to a row at a given physical offset beyond that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

The following is an example to illustrate the lag window function. The table monthly_sales is as follows:

 YEAR | MONTH_NUM |  SALES  
------+-----------+---------
 2011 |         1 | 1000.00
 2011 |         2 |  900.00
 2011 |         3 | 1200.00
 2011 |         4 | 1200.00
 2011 |         5 | 1250.00
 2011 |         6 | 1200.00
 2011 |         7 |  800.00
 2011 |         8 | 1200.00
 2011 |         9 | 1400.00
 2011 |        10 | 1450.00
 2011 |        11 | 1500.00
 2011 |        12 | 1800.00
 2012 |         1 | 1600.00
 2012 |         2 | 1500.00
 2012 |         3 | 1400.00
 2012 |         4 | 1700.00

Within a year, you can compare the sales of a month to the previous month, as in the following example:

SELECT year, month_num, sales as current_month_sales, LAG(sales,1) 
OVER (partition by year order by month_num) prior_month_sales, 
ROUND(100*(current_month_sales - 
prior_month_sales)/prior_month_sales,1) percentage_increase from 
monthly_sales ORDER BY year, month_num;
YEAR | MONTH_NUM | CURRENT_MONTH_SALES | PRIOR_MONTH_SALES | 
PERCENTAGE_INCREASE 
------+-----------+---------------------+-------------------+--------
 2011 |         1 |             1000.00 |                   | 
 2011 |         2 |              900.00 |           1000.00 | -10.0
 2011 |         3 |             1200.00 |            900.00 | 33.3
 2011 |         4 |             1200.00 |           1200.00 | 0.0
 2011 |         5 |             1250.00 |           1200.00 | 4.2
 2011 |         6 |             1200.00 |           1250.00 | -4.0
 2011 |         7 |              800.00 |           1200.00 | -33.3
 2011 |         8 |             1200.00 |            800.00 | 50.0
 2011 |         9 |             1400.00 |           1200.00 | 16.7
 2011 |        10 |             1450.00 |           1400.00 | 3.6
 2011 |        11 |             1500.00 |           1450.00 | 3.4
 2011 |        12 |             1800.00 |           1500.00 | 20.0
 2012 |         1 |             1600.00 |                   | 
 2012 |         2 |             1500.00 |           1600.00 | -6.3
 2012 |         3 |             1400.00 |           1500.00 | -6.7
 2012 |         4 |             1700.00 |           1400.00 | 21.4
(16 rows)