LAG() vs. LEAD() functions in SQL

In SQL, the LAG() and LEAD() methods are window functionsThese calculate values for each row in a query result set based on a specified window of rows. that allow us to access data from previous and subsequent rows within a specified window, respectively. By comparing LAG() and LEAD(), we can understand how these window functions operate and the valuable insights they offer. This comparison will clarify the syntax and functionality of each function and showcase practical examples using a sample database.

The LAG() method

Let’s first create a sample database named sales_data and then demonstrate the LAG() method function.

How to create a sample database

Let’s quickly go over creating a sample database:

-- Creating a dummy database named 'sales_data'
CREATE TABLE sales_data (
    month VARCHAR(10),
    revenue DECIMAL(10, 2)
);
-- Inserting sample data into the 'sales_data' table
INSERT INTO sales_data (month, revenue) VALUES
('Jan', 1000),
('Feb', 1200),
('Mar', 1500),
('Apr', 1100),
('May', 1300),
('Jun', 1600);
SELECT * from sales_data;

  • Lines 2–5: Here, we create a table named sales_data with two columns: month of type VARCHAR(10) and revenue of type DECIMAL(10, 2).

  • Lines 8–14: The VALUES clause inserts sample data into the sales_data table for the months 'Jan' through 'Jun' along with corresponding revenue values.

  • Line 16: This line executes a SELECT statement to retrieve and display all data from the sales_data table.

Now that we have our sample data let's explain LAG() function's syntax and functionality.

Syntax

The LAG() function's syntax is as follows:

LAG(column_expression, offset_N, default_value) OVER (ORDER BY ordering_expression)
Syntax for the LAG() function

  • column_expression: The column for which we want to retrieve the lagged value.

  • offset_N (Optional): The number of rows before the current row from which to retrieve the value (default is 1).

  • default_value (Optional): The value to return if the offset goes beyond the available rows (default is NULL).

  • ORDER BY ordering_expression: Specifies the order in which the data is considered.

Note: It's essential to use the OVER()It defines how rows are grouped and sorted in the window. clause with this function, as omitting it may lead to errors.

Example

Below is the example showcasing the implementation of LAG() function:

-- Retrieve each month's revenue along with the revenue from the previous month using the LAG() method
SELECT
    month,
    revenue,
    LAG(revenue) OVER () AS previous_month_revenue
FROM
    sales_data;

  • Line 6: We use the LAG(revenue) method to retrieve the revenue from the previous month.

  • Line 7: The OVER clause with () ensures the correct order of data in accordance with the value of month.

The previous_month_revenue column in the output will display the revenue from the preceding month for each row in the result set, with the first row showing NULL as there is no previous month’s revenue.

Using the offset_N and default_value

The following widget implements the LAG() function with offset_N value of 2 and a default_value of 0.

Note: Try changing the value of offset_N and default_value in the widget below and see what affect does it generate in the output.

-- Retrieve each month's revenue along with the revenue from the previous month using LAG()
SELECT
    month,
    revenue,
    LAG(revenue, 2, 0::numeric) OVER () AS previous_month_revenue
FROM
    sales_data;

The LEAD() method

Similar to the LAG() function, let's go over the LEAD() function's syntax and functionality.

Syntax

The LEAD() function’s syntax is as follows:

LEAD(column_expression, offset_N, default_value) OVER (ORDER BY ordering_expression)
Syntax for the LEAD() function

  • column_expression: This is the column for which we want to retrieve the leading value.

  • offset_N (Optional): These are the number of rows after the current row from which to retrieve the value (default is 1).

  • default_value (Optional): This is the value to return if the offset goes beyond the available rows (default is NULL).

  • ORDER BY ordering_expression: This specifies the order in which the data is considered.

Note: It's essential to use the OVER() clause with this function, as omitting it may lead to errors.

Example

Below is the example showcasing the implementation of LEAD() function:

-- Retrieve each month's revenue along with the revenue from the next month using LEAD()
SELECT
    month,
    revenue,
    LEAD(revenue) OVER () AS next_month_revenue
FROM
    sales_data;

  • Line 6: Similar to LAG(), we use the LEAD(revenue) method to obtain the revenue from the next month.

  • Line 7: The OVER clause with () ensures the correct order of data in accordance with the value of month.

The next_month_revenue column in the output will display the revenue from the subsequent month for each row in the result set, with the last row showing NULL as there is no revenue for the next month`

Comparison example

Now, let’s illustrate the side-by-side difference between LAG() and LEAD() by applying them simultaneously to our sales_data table scenario.

-- Compare each month's revenue with the previous and next months
SELECT
    month,
    to_char(revenue, '99999.99') AS revenue,
    to_char(LAG(revenue) OVER (), '99999.99') AS previous_month_revenue,
    to_char(LEAD(revenue) OVER (), '99999.99') AS next_month_revenue
FROM
    sales_data;

  • Line 6: We use the LAG(revenue) method to get the revenue from the previous month.

  • Line 7: Similarly, LEAD(revenue) retrieves the revenue from the next month.

  • Line 8: The OVER clause with (ORDER BY month) ensures the correct order of data.

Note: We utilized the to_char function with the '99999.99' format pattern to uniformly display the revenue, previous_month_revenue, and next_month_revenue variables. This formatting enhances readability by ensuring consistent presentation with two decimal places and leading zeros.

This example demonstrates how LAG() and LEAD() can be employed to compare values from adjacent rows in a result set.

Conclusion

In conclusion, while LAG() looks backward, LEAD() looks forward within a specified window, providing valuable insights for analytical tasks in SQL. Here is a table summarizing the differences between two:

Function Name

Functionality

Syntax

Optional Parameters

LAG()

Retrieves lagged values

LAG(column_expression, offset_N, default_value) OVER (ORDER BY ordering_expression)

offset_N, default_value

LEAD()

Retrieves leading values

LEAD(column_expression, offset_N, default_value) OVER (ORDER BY ordering_expression)

offset_N, default_value

Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved