SQL - LEAD and LAG functions to calculate differences between different rows

Reference:
https://www.mssqltips.com/sqlservertutorial/9127/sql-server-window-functions-lead-and-lag/

Usage in industry:

For example, you have a lot of data from directional survey, and you have data measured depth, and inclination. You would like to know the first inclination point which turns or greater than 80, since that might be the place where the well becomes directional. And the length till that point would be vertical depth, total measured depth - depth till that point is lateral length. But you need to check the distance between that point (in my example P2) and its previous point (P1) to make sure that the distance is less that 200, then that confirms the data is right (at least the percentage of data accuracy is high). In this case, you can use Lag() function.

So, by using lag() function, you can do:

select api, max_MD,Lag(DS_MD,1) OVER(PARTITION BY api ORDER BY DS_MD) as previous_VD
from.....