Numbering and ranking look at the whole window at once. Sometimes a row only needs to see its neighbour: how many days passed between two badges of the same trainer? LAG(column) fetches the value of the previous row, following the window's ORDER BY:
↗️
Loading editor...
↶
↷
Each badge now carries the date of the one before it. The first badge of each trainer has no previous one, so LAG returns NULL there — and thanks to PARTITION BY trainer_id, Morgan's badges never look at Alex's.
With both dates on the same row, the gap is a subtraction with julianday():
↗️
Loading editor...
↶
↷
Morgan's rhythm slowed down: 13 days, then 15, then 23 and 23. Alex waited 25 days, then 15; Taylor 11.
Offset and default
LAG(x, 2) looks two rows back; LAG(x, 1, 0) replaces the missing value with 0 instead of NULL. LEAD takes the same three arguments but looks forward.