Lesson 19.2

LAG and LEAD in SQL

LAG reads a value from the row before, LEAD from the row after, within each partition. It's how you measure gaps and changes between rows.

Some questions compare a row with its neighbor: how long since this customer's last order, how much a reading changed since yesterday, whether this month beat last month. lag() reads a value from the row before the current one, and lead() from the row after, within the same window.

Pattern

select account_id,
       reading_date,
       reading,
       reading - lag(reading) over (
         partition by account_id
         order by reading_date
       ) as change_since_last
from meter_readings

The reusable shape is lag(value) over (partition by group_key order by sequence_key). Change lag to lead to look forward instead of backward. The function chooses the value; the window decides which row is previous or next.

What "before" means

The window's ORDER BY decides which row is previous, and PARTITION BY keeps each group's history separate, so one account's first reading never looks back into another account's last. Add a tie-breaker when two rows can share the ordering value; otherwise which one counts as "previous" is up to the engine.

Looking forward with LEAD

lead() is the mirror image. Here each order shows the date of the same customer's next order:

Next order with LEAD

select customer_id,
       order_id,
       order_date,
       lead(order_date) over (
         partition by customer_id
         order by order_date, order_id
       ) as next_order_date
from orders
where customer_id in (1, 3)
order by customer_id, order_date

Customer 1's last order and customer 3's last order have no next order, so they read NULL.

The NULL on the edge

The first row of each partition has nothing before it, so lag() returns NULL there. That's usually right: "no previous order" isn't the same as "zero days". If you do want a stand-in, the third argument supplies it, and the second reaches further back: lag(reading, 1, 0) uses 0 when there is no previous row, and lag(reading, 7) reads the row seven back.

Subtracting two dates gives the whole number of days between them, in DuckDB and PostgreSQL alike. date_diff('day', a, b) gives the same number in DuckDB.

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Syntax reference

Need syntax details? See the SQL window function reference

Your task

For every order, show how many days had passed since the same customer's previous order. Return customer_id, order_id, order_date, and days_since_previous (NULL for a customer's first order). Sort by customer_id, then order_date.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: customer_id, order_id, order_date, days_since_previous.
Rows: all 24 orders, each customer's oldest first.
A customer's first order has no previous one, so it shows NULL. Customer 1's second order came 77 days after the first.

Previous · 19.1 Ranking and ties

Next · 19.3 Running totals →

Reference

SQL Window Functions

OVER, PARTITION BY, ranking, offsets, and running totals.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview

SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake