SQL Server LAG() Function

The SQL Server LAG() function is a window function that can be used to access the previous row of data in a query result set. This function can be used to calculate the difference between the current row and the previous row, or to compare the data in the previous row with the current row. The LAG() function is very useful when analyzing data and can help analysts identify trends and patterns in the data.

Syntax

The syntax for the LAG() function is as follows:

LAG(expression [,offset] [,default]) OVER ( [partition_by_clause] order_by_clause )

Where:

expression: the column or expression that you want to access the previous row data of.
offset: the offset of the previous row to be accessed, defaults to 1. For example, if the offset is 2, the LAG() function will access the previous two rows of data.
default: the default value to be used if the previous row data to be accessed does not exist. Defaults to NULL.
partition_by_clause: optional parameter to group the data by column or expression.
order_by_clause: specifies the order of the columns or expressions in the query result set.

Usage

The LAG() function is suitable for the following scenarios:

Calculating the difference between the current row and the previous row.
Comparing the data in the previous row with the current row.
Identifying trends and patterns in data.
Using sliding window functions when analyzing data.

Examples

Here are two examples of the LAG() function, including sample data and results.

Example 1

Assume there is a table named sales that contains the following data:

year	quarter	sales
2020	Q1	100
2020	Q2	200
2020	Q3	300
2020	Q4	400
2021	Q1	500
2021	Q2	600
2021	Q3	700
2021	Q4	800

We can use the LAG() function to calculate the sales growth rate for each quarter. Here is the query and the result:

SELECT year,
  quarter,
  sales,
  LAG(sales, 1) OVER (
    ORDER BY year,
      quarter
  ) AS prev_sales,
  (
    sales - LAG(sales, 1) OVER (
      ORDER BY year,
        quarter
    )
  ) / LAG(sales, 1) OVER (
    ORDER BY year,
      quarter
  ) AS growth_rate
FROM sales;

Result:

year	quarter	sales	prev_sales	growth_rate
2020	Q1	100	NULL	NULL
2020	Q2	200	100	1.0000
2020	Q3	300	200	0.5000
2020	Q4	400	300	0

Example 2

Suppose there is a table named employees containing the following data:

employee_id	department	salary
1	A	5000
2	A	6000
3	B	7000
4	B	8000
5	B	9000
6	C	10000

We can use the LAG() function to calculate the salary growth rate for each employee within their department. The following query statement and result are shown below:

SELECT employee_id,
  department,
  salary,
  LAG(salary, 1, 0) OVER (
    PARTITION BY department
    ORDER BY salary
  ) AS prev_salary,
  (
    salary - LAG(salary, 1, 0) OVER (
      PARTITION BY department
      ORDER BY salary
    )
  ) / LAG(salary, 1, 0) OVER (
    PARTITION BY department
    ORDER BY salary
  ) AS growth_rate
FROM employees;