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.

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_date

Each 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.

All BigQuery lessons