SQL Windows Functions - Part 3 (LAG function in detail)

 LAG function

It returns the value from a previous row to the current row in the table.

Step1 : Write the Inner query

SELECT     account_id,  SUM(standard_qty) AS standard_sum FROM  orders GROUP BY   1

Step2: Build outer query and name inner query as sub

Step3:

Add the Window Function OVER (ORDER BY standard_sum) in the outer query that will create a result set in ascending order based on the standard_sum column.

To get the lag_difference

SELECT account_id,
       standard_sum,
       LAG(standard_sum) OVER(ORDER BY standard_sum) AS lag,
       LEAD(standard_sum) OVER (ORDER BY standard_sum) AS lead,
       standard_sum - LAG(standard_sum) OVER (ORDER BY standard_sum) AS lag_difference,
       LEAD(standard_sum) OVER (ORDER BY standard_sum) - standard_sum AS lead_difference
FROM (
       SELECT account_id,
              SUM(standard_qty) AS standard_sum
       FROM orders
       GROUP BY 1
) sub

Source :Udacity and https://www.windowfunctions.com/questions/grouping/1