Skip to main content

Window functions: LAG

Premium

One of the most commonly used window functions is LAG, which allows you to retrieve data from a previous row within a query result set. This can be useful for calculating differences between rows, such as the difference in sales from one day to the next.

Basic Syntax

Here's the basic syntax of the LAG function:

SQL
LAG(value, offset, default) OVER (ORDER BY column);

The value argument is the column that you want to retrieve data from. The offset argument specifies the number of rows to retrieve data from. For example, if you specify an offset of 1, LAG will retrieve data from the previous row. The default argument specifies a default value to use if there is no previous row to retrieve data from.

Example: Calculating Daily Change in Sales

Consider the following table, sales, which contains daily sales data for a store:

| date       | sales |
|------------|-------|
| 2022-01-01 | 100   |
| 2022-01-02 | 150   |
| 2022-01-03 | 125   |
| 2022-01-04 | 130   |

Suppose we want to calculate the difference between today's sales and yesterday's sales. We can use the LAG function for this, as follows:

SELECT
  date,
  sales,
  sales - LAG(sales, 1, 0) OVER (ORDER BY date) AS change
FROM sales;

The result will be:

| date       | sales | change |
|------------|-------|--------|
| 2022-01-01 | 100   | 0      |
| 2022-01-02 | 150   | 50     |
| 2022-01-03 | 125   | -25    |
| 2022-01-04 | 130   | 5      |

In this example, the LAG function is used to retrieve the previous day's sales, and the difference between the current day's sales and previous day's sales is calculated. Note that the first row has a delta of 0, as there is no previous row to retrieve data from.

The LAG function is a useful tool for calculating differences between rows in a query result set. By using the LAG function, you can retrieve data from previous rows and use it in calculations, making it a powerful tool for data analysis.

Try it yourself

Let's try using the LAG window function in practice. Using our orders table from before, write a query that returns a list of orders plus a new column previous_order_id that is populated with the order ID of that same customer's last order (if one exists).

Next Lesson