LAG and LEAD for row comparison in PostgreSQL

Introduction

LAG and LEAD help you look at data from other rows in the same table. This is useful to compare values between rows easily.

To find the difference between a current row and the previous row, like daily sales changes.

To compare a value with the next row, such as checking if a stock price went up or down.

To see trends over time by comparing each row with the one before or after.

To find gaps or jumps in data by looking at neighboring rows.

To create reports that show changes between rows without complicated joins.

Syntax

PostgreSQL

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

LAG looks at a previous row's value.

LEAD looks at a next row's value.

Offset is how many rows back or forward to look (default is 1).

Default value is used if there is no row to look at.

Examples

This shows sales and the previous day's sales for comparison.

PostgreSQL

SELECT sales, LAG(sales) OVER (ORDER BY date) AS prev_sales FROM sales_data;

This shows the price and the next day's price, using 0 if no next day exists.

PostgreSQL

SELECT date, price, LEAD(price, 1, 0) OVER (ORDER BY date) AS next_price FROM stock_prices;

This looks two rows back in the same department to compare salaries.

PostgreSQL

SELECT employee, salary, LAG(salary, 2) OVER (PARTITION BY department ORDER BY employee) AS salary_two_before FROM employees;

Sample Program

This example creates a sales table and shows each day's sales, the previous day's sales, and the next day's sales for easy comparison.

PostgreSQL

CREATE TABLE sales_data (date DATE, sales INT);
INSERT INTO sales_data VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 130),
('2024-01-04', 170);

SELECT date, sales,
       LAG(sales) OVER (ORDER BY date) AS prev_sales,
       LEAD(sales) OVER (ORDER BY date) AS next_sales
FROM sales_data;

Important Notes

If there is no previous or next row, LAG or LEAD returns NULL or the default value you set.

Always use ORDER BY inside OVER() to define the row order for comparison.

You can use PARTITION BY to restart the comparison for groups, like departments or categories.

Summary

LAG and LEAD let you peek at other rows to compare values easily.

Use ORDER BY to control which rows are compared.

They help find trends, changes, or gaps in your data without complex joins.