Excellent writeup. I remember having to do similar queries on tachograph readings from an onboard computer aggregation system for fleets of trucks. We had to calculate remaining times the driver was allowed to drive and when they had to take a rest and such. The data was not exactly clean and required a bit of massaging, exactly like the events in the blog post.

Wish I’d known about first_value and last_value, IIRC back then I didn’t know about those and did it manually by selecting the max and min values of rank.