What is the difference between Lead and Lag functions
The Lead function returns values from the next row, while the Lag function returns values from a previous row.
Example
Select start_date, lead (start_date,1) over (ORDER BY start_date) AS next_start_date lag (start_date,1) over (ORDER BY start_date) AS prev_start_date From contracts Where customer_id = 760;
Start_Date | Next_Start_Date | Prev_Start_Date |
20/02/2011 | 22/02/2011 | NULL |
22/02/2011 | 23/02/2011 | 20/02/2011 |
23/02/2011 | NULL | 22/02/2011 |