SQL Analytical Functions - LAG, LEAD (Compare current row with previous/next row(s))

LAG:

Returns data from the previous row(s). 

Employees -Table

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        100 Steven                    24000 
        101 Neena                     17000 
        102 Lex                       17000 
        103 Alexander                  9000 
        104 Bruce                      6000 
        105 David                      4800 
        106 Valli                      4800 
        107 Diana                      4200 

1) Below query compares current row with previous row for displaying results

select e.employee_id, e.first_name, e.salary, 
    lag(salary, 1, 0)  over (order by salary) as salary_prev,
    e.salary -lag(salary, 1, 0)  over(order by salary)  as salary_diff
    from employees e ;

lag(salary, 1, 0) 

'salary' - Row to be compared.

'1' - Indicates which row to be compared.'1' compares current row with 

one row previous to the current row. if this value is 2, then 2 rows previous to the current

row will be compared.

'0' - Default value when the salary is not found for a row

over (order by salary) - Order the rows by salary

lag(salary, 1, 0)  over (order by salary) - Returns previous row record

EMPLOYEE_ID FIRST_NAME               SALARY SALARY_PREV SALARY_DIFF
----------- -------------------- ---------- ----------- -----------
        107 Diana                      4200           0        4200 
        106 Valli                      4800        4200         600 
        105 David                      4800        4800           0 
        104 Bruce                      6000        4800        1200 
        103 Alexander                  9000        6000        3000 
        101 Neena                     17000        9000        8000 
        102 Lex                       17000       17000           0 
        100 Steven                    24000       17000        7000 

Usage:
2) Used to find out the difference in salary between employees from the same department.

select e.department_id, e.employee_id, e.first_name, e.salary, 
    lag(salary, 1, 0) over(partition by e.department_id order by salary) as salary_prev,
    e.salary -lag(salary, 1, 0) over(partition by e.department_id order by salary)  as salary_diff
    from employees e;

DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME               SALARY SALARY_PREV SALARY_DIFF
------------- ----------- -------------------- ---------- ----------- -----------
           60         107 Diana                      4200           0        4200 
           60         106 Valli                      4800        4200         600 
           60         105 David                      4800        4800           0 
           60         104 Bruce                      6000        4800        1200 
           60         103 Alexander                  9000        6000        3000 
           90         101 Neena                     17000           0       17000 
           90         102 Lex                       17000       17000           0 
           90         100 Steven                    24000       17000        7000 

LEAD:

Returns data from the next row(s).

3) Below query compares current row with next row for displaying results

select e.employee_id, e.first_name, e.salary, 
    lead(salary, 1, 0)  over (order by salary) as salary_next,
    lead(salary, 1, 0)  over(order by salary) - e.salary as salary_diff

from employees e ;

EMPLOYEE_ID FIRST_NAME               SALARY SALARY_NEXT SALARY_DIFF
----------- -------------------- ---------- ----------- -----------
        107 Diana                      4200        4800         600 
        106 Valli                      4800        4800           0 
        105 David                      4800        6000        1200 
        104 Bruce                      6000        9000        3000 
        103 Alexander                  9000       17000        8000 
        101 Neena                     17000       17000           0 
        102 Lex                       17000       24000        7000 
        100 Steven                    24000           0      -24000