Analytic Functions in SQL

Functions :

1. LEAD

LEAD function is used to fetch the data from the next row. And provide a way to do the manipulation with the use of next row.

Syntax:  LEAD(value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) 

For example: If we need to find out the difference between quantity of all the products present in SALES table with respect to next year.

select product_id,year,quantity,
lead(quantity, 1, 0) over (partition by product_id order by year) as prev_quantity,
quantity-lag(quantity, 1, 0) over (partition by product_id order by year) as diff

from sales;

2. LAG

LAG function is used to fetch the data from previous row. And provide a way to do the manipulation with the use of previous row.

Syntax:  LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) 

value_expression : It can be a column or builtin function.
offset: Number of rows preceding. Default value is 1
default: Value returned when offset will be out of scope.

For example: If we need to find out the difference between quantity of all the products present in SALES table with respect to previous year.

select product_id,year,quantity,
lag(quantity, 1, 0) over (partition by product_id order by year) as prev_quantity,
quantity-lag(quantity, 1, 0) over (partition by product_id order by year) as diff
from sales;

3. LISTAGG :

This function is used to aggregate the strings and can be use to sort them.

For Example: If we want to see the how many products are mapped with a particular product_id and we want to display the product names by comma separated. 

select product_id,listagg(product_name, ',') within group(order by product_name) product_list
from products
group by product_id;