LAG/LEAD are basically SQL having to give in a little to the timeseries and column db people. It obliterates the set theoretic nature of SQL, and they are dog slow.

This is why any decent columnar/timeseries db has its own often arrish query language to take advantages of ordering information inherent in the data. There is an order in that index and SQL table, you just don't have good tools to access and use it.

This touches on a conversation I was having a couple weeks ago where somebody was saying that column/array storage is purely a backend consideration and don't need to care about it in the front end of the database. That view gets you ugly primitives like LAG/LEAD. Being able to exploit your backend decision is paramount to performance.

Products like Informix or KDB+ just give you full access to the data on and off disk as an array so you ca build complex windowing primitives (and even store them and reuse them later).

(PS: I just find this really funny. KDB+ is probably is the only database I know that literally does streaming analytics on a Raspberry Pi. No joke, the full DB on Linux ARM/Pi. https://kx.com/download/ )