Skip to main content


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.


Using the LAG analytic function it is possible to refer to the prior record.



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.

Physical vs Logical Offset

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.

SQL Generation

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.