SQL Academy · Lesson

LAG, LEAD and Time-Series Patterns

Compare each row to its previous or next sibling with LAG and LEAD, and build period-over-period deltas.

LAG and LEAD: Look Backwards / Forwards

These access neighbouring rows in the window:

Compare each order to the previous one for the same user:

SELECT id, user_id, created_at, total,
       LAG(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS prev_total
FROM orders;