LAG, LEAD & date gaps

Compare with the previous row

Lesson 10 of 20 · 10 min · Module 3: Window functions in depth

How long do customers wait between purchases? Did revenue go up compared with last month? These questions compare a row with its neighbour, which LAG and LEAD make easy.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Read a neighbouring row with LAG and LEAD
Restart comparisons per group with PARTITION BY
Calculate gaps between dates

The idea

LAG(column) OVER (PARTITION BY … ORDER BY …) returns the value from the previous row of the same partition, or NULL for the first row. LEAD looks at the next row. Subtract julianday values to get the number of days between two dates.

SELECT id, ordered_at,
       LAG(ordered_at) OVER (ORDER BY ordered_at, id) AS previous_order
FROM orders
LIMIT 3;

01Partitions restart the window

PARTITION BY o.customer_id makes LAG look only at the same customer’s orders. The first order of each customer has no previous row, so LAG returns NULL.

02NULL flows through

Subtracting anything from NULL gives NULL, so first orders automatically show NULL instead of a misleading number.

SELECT 5 - NULL;

Output

NULL

03Filters apply first

The WHERE clause removes cancelled orders before LAG runs, so a cancelled order never counts as the "previous" purchase.

Your turn

For every order that is not cancelled, return the customer’s name, ordered_at, and days_since_previous: the whole number of days since that customer’s previous non-cancelled order, or NULL for their first. Sort by name, then date.

What the checks look for

Shows the gap between each customer’s orders

Try it in the editor →

Take this with you

LAG and LEAD compare a row with its neighbours inside a partition.

Go a little further

Use LEAD to show the date of each customer’s next order next to the current one.