LAG, running counts and frames
How far did a car drive since its last test? That question compares each row with its neighbour, and LAG answers it without a self join.
How it works
Take one vehicle and put its tests in date order. LAG looks one row back in that order and returns a value from it. An aggregate with an ORDER BY inside OVER grows as the rows go by, which gives a running result. A frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the aggregate to the current row and the two before it.
LAG(x) OVER (PARTITION BY vehicle_id ORDER BY ...)returnsxfrom the previous row of the same vehicle.LEAD(x)returns it from the next row. The first row has no previous row, soLAGgivesNULLthere.PARTITION BYrestarts the sequence for each vehicle. Without it, the previous row can belong to a different vehicle.- The window
ORDER BYmust give one order.completed_datealone leaves tests from the same day tied, so addcompleted_timestampand thenmot_test_id. - Two rows that tie on the window
ORDER BYget the same running total, because the default frame covers every tied row at once. That default isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. WriteROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWto count one row at a time instead. - The window only sees the rows that survive
WHERE. Thecompleted_datefilter also decides which earlier testLAGcan find, and a four year range reads 48 monthly partitions.
Common mistake
A LAG with PARTITION BY but an incomplete ORDER BY still runs and returns plausible numbers. When two tests share a date, which one counts as the previous one then depends on how the tie falls.
Worked example
SELECT vehicle_id, completed_date,
LEAD(completed_date) OVER (PARTITION BY vehicle_id ORDER BY completed_date, completed_timestamp, mot_test_id) AS next_test_date
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2023-01-01' AND '2024-12-31'
AND vehicle_id = 'VEH000002'
ORDER BY completed_dateEach test of VEH000002 gets the date of the test that follows it. The last test has no next row, so its next_test_date is NULL.
The filter on completed_date keeps the read to 24 monthly partitions and decides which tests the window can see.