How much did the amount change compared with the previous order? What is this customer’s next purchase? Answering requires looking at another row of the result, and that’s what LAG (the previous row) and LEAD (the next one) do. They are window functions, so they always go with OVER and an ORDER BY that defines what “previous” and “next” mean.

Syntax
LAG(column [, offset [, defaultValue]]) OVER ([PARTITION BY ...] ORDER BY ...)
LEAD(column [, offset [, defaultValue]]) OVER ([PARTITION BY ...] ORDER BY ...)
  • offset: how many rows back or forward to look. Defaults to 1.
  • defaultValue: what to return when that row doesn’t exist (the first row for LAG, the last for LEAD). Defaults to NULL.

The previous and next order

We sort the orders by date and show, next to each one, the product ordered just before and just after:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
orderDate product previous next
2022-01-21 Xbox series X NULL MacBook Pro M1
2022-06-20 MacBook Pro M1 Xbox series X Xiaomi Mi 11
2022-07-09 Xiaomi Mi 11 MacBook Pro M1 Play Station 5
2022-07-18 Play Station 5 Xiaomi Mi 11 Echo DOT 3
2022-09-01 Echo DOT 3 Play Station 5 Echo DOT 4
2022-09-01 Echo DOT 4 Echo DOT 3 Nintendo Switch
2022-09-05 Nintendo Switch Echo DOT 4 Balay Oven
2022-09-10 Balay Oven Nintendo Switch NULL

The first order has no previous one and the last has no next one, hence the NULLs.

Difference from the previous row

The most common use: subtracting the previous value from the current one to see how much it went up or down. Here with each order’s amount, using 0 as the default so the first row isn’t NULL:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
orderDate product totalPrice previousAmount change
2022-01-21 Xbox series X 999.98 0 999.98
2022-06-20 MacBook Pro M1 2449.50 999.98 1449.52
2022-07-09 Xiaomi Mi 11 4304.25 2449.50 1854.75
2022-07-18 Play Station 5 2199.80 4304.25 -2104.45
2022-09-01 Echo DOT 3 1999.50 2199.80 -200.30
2022-09-01 Echo DOT 4 2999.50 1999.50 1000.00
2022-09-05 Nintendo Switch 899.97 2999.50 -2099.53
2022-09-10 Balay Oven 1399.10 899.97 499.13

With monthly sales, the same query would give you month-over-month growth; with stock prices, the daily change.

Per group with PARTITION BY

With PARTITION BY, LAG and LEAD never leave the group. Each customer’s first purchase has no previous one, even if other customers ordered earlier:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name orderDate product previousPurchase
Helen 2022-09-01 Echo DOT 3 NULL
Helen 2022-09-01 Echo DOT 4 2022-09-01
Janice 2022-06-20 MacBook Pro M1 NULL
Kimberly 2022-09-05 Nintendo Switch NULL
Mavin 2022-01-21 Xbox series X NULL
Mavin 2022-07-18 Play Station 5 2022-01-21
Peter 2022-07-09 Xiaomi Mi 11 NULL

Only Helen and Mavin have more than one order, so they are the only ones with a previous purchase date. Helen ordered both Echo DOTs on the same day.

To get the number of days between a purchase and the previous one, SQL Server uses DATEDIFF(day, previousPurchase, orderDate). Date functions vary a lot between databases; in PostgreSQL, for example, you just subtract the two dates.

FIRST_VALUE and LAST_VALUE

While LAG and LEAD look at a fixed distance, FIRST_VALUE returns the first value of the window. For example, the first product each customer bought, repeated on all their rows:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name orderDate product firstPurchase
Helen 2022-09-01 Echo DOT 3 Echo DOT 3
Helen 2022-09-01 Echo DOT 4 Echo DOT 3
Janice 2022-06-20 MacBook Pro M1 MacBook Pro M1
Kimberly 2022-09-05 Nintendo Switch Nintendo Switch
Mavin 2022-01-21 Xbox series X Xbox series X
Mavin 2022-07-18 Play Station 5 Xbox series X
Peter 2022-07-09 Xiaomi Mi 11 Xiaomi Mi 11
LAST_VALUE is misleading: with an ORDER BY in the OVER, the default window runs from the start up to the current row, so the “last” value is always the row’s own. To get the real last value you have to widen the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. It’s explained in running totals.

Practice

  1. For each order, show the product ordered two orders earlier (LAG with offset 2).
  2. Work out each order’s amount change compared with the next one.
  3. For each customer, show their latest purchase on every row using FIRST_VALUE with the order reversed.