The SQL LAG() function is a window function used to access a column’s value from a previous row within a defined partition and order. Unlike aggregate functions, it does not reduce the number of rows; instead, it returns a corresponding value for each row. By specifying a partition (e.g., by organisation) and an order (e.g., by year), LAG() enables row-by-row comparisons, such as comparing current values with prior-period values
E.g
SELECT
Department,
Employee,
SalaryMonth,
Salary,
LAG(Salary, 1, 0)
OVER (PARTITION BY Department ORDER BY SalaryMonth) AS PrevMonthSalary
FROM EmployeeSalary
ORDER BY Department, SalaryMonth
Result
| Department | Employee | SalaryMonth | Salary | PrevMonthSalary |
|---|---|---|---|---|
| IT | John | 2024-01 | 120000 | 0 |
| IT | John | 2024-02 | 125000 | 120000 |
| IT | John | 2024-03 | 130000 | 125000 |
| HR | Mary | 2024-01 | 90000 | 0 |
| HR | Mary | 2024-02 | 92000 | 90000 |
| HR | Mary | 2024-03 | 95000 | 92000 |
