Shift/Lag
SQL also has analytic functions that provide the ability to refer to previous or next row of data relative to current row when the data is ordered.
LAG
Using the LAG analytic function it is possible to refer to the prior record.
LEAD
Using the LEAD analytic function it is possible to refer to the following record.
Difference from Prior Year
Below is an example of showing a chart with current values and comparing it with prior data.
The data is compared to prior record and in most cases it is the prior year. However, if data is sparse as in this case, a range window instead of a row window needs to be used.
Dynamic Offset for LAG/LEAD
Most databases only suppot a fixed offset value for the LAG and LEAD SQL analytic functions. SQL Frames
supports dynamic LAG and LEAD functions where the offset is based on another column.
This advanced feature makes it easy for certain type of problems but it should be noted that it will not produce a valid SQL for most databases including Oracle, Snowflake, PostgreSQL and MySQL.