Lag Lead Functions

LAG() and LEAD() are window functions used to retrieve data from rows before and after a specified row. You can also refer to them as positional SQL functions.

LAG() allows you to access a value stored in rows before the current row. The row may be directly before or some rows before. Let's take a look at the syntax:

LAG(column_name, offset, default_value)

It takes three arguments.

• column_name: This specifies the column to fetch from the previous row.
• offset: This is an optional argument and specifies the number of rows behind to look at. The default is 1.
• default_value: This is the value to assign when no previous row exists. It is optional, and the default is NULL.

Using the Sales table, let's illustrate the LAG() function. The query is used to find the previous day sales. LAG() is useful when you want to create reports of past events.

SELECT
  id,
  day,
  amount,
  LAG(amount) OVER (ORDER BY id) AS previous_day_sales
FROM
  sales;

The result of the query looks like this:

You use the LEAD() function to get data from rows after the current row. Its syntax is similar to that of the LAG() function. You can use it for forecasting future trends by looking ahead.

The query using the LEAD() function is shown below.

SELECT
  id,
  day,
  amount,
  LEAD(amount) OVER (ORDER BY id) AS previous_day_sales
FROM
  sales;