SQL LEAD and LAG()

The “LEAD ()” function returns the next incoming value based on the specified offset value. If no such value exists, it returns “default”. Returns “NULL” if “default” is not entered in the function. In addition, if no [offset value] is entered in our function, default is set to 1.

The LAG () function works in the opposite way with the LEAD () function. So it returns the value before it.

SELECT cus.first_name,cus.last_name, 
LAG(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name)
FROM BikeStores.sales.customers as cus;

SQL LEAD and LAG()

---

SELECT cus.first_name,cus.last_name, 
LEAD(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name)
FROM BikeStores.sales.customers as cus;

SQL LEAD and LAG()

---

SELECT cus.first_name,cus.last_name, 
LAG(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name) as [LAG COL],
LEAD(cus.first_name, 1 ,'empty') OVER(PARTITION BY cus.last_name ORDER BY cus.last_name) as [LEAD COL]
FROM BikeStores.sales.customers as cus;

SQL LEAD and LAG()