How do LAG/LEAD answer “what did this row look like yesterday”?

Category: SQL for AI Engineering

Answer

LAG(col, 1) over (partition by … order by ts) returns col from the previous row in the same partition; LEAD(col, 1) returns the next. Useful for diffs (sales - prev_sales), change detection, lookahead features.

Concrete examples from the fca project context

Example 1

daily_sales - LAG(daily_sales, 1) OVER (PARTITION BY sku ORDER BY day) gives daily change.

Example 2

LEAD(ts, 1) returns the next event timestamp for time-to-next-event calculations.

Example 3

Default value: LAG(col, 1, 0) -> 0 when no previous row exists.

Data flow / flow chart

partition by sku order by day:
   day   sales  lag(sales)
   d-1   100    -        (default 0)
   d     120    100
   d+1   115    120

Takeaway

LAG/LEAD turn time-series into “compare with neighbor in O(1) SQL”. No self-join needed.