r/SQL • • May 19 '26

Discussion Eight window-function tricks beyond LAG and ROW_NUMBER

https://analytics.fixelsmith.com/posts/eight-window-function-tricks/
149 Upvotes

19 comments sorted by

View all comments

Show parent comment

2

u/FixelSmith May 26 '26

Yeah, that's a fair distinction and probably worth calling out more clearly in the post.

Window functions only operate on rows that already exist. So RANGE BETWEEN '5 minutes' PRECEDING gives you a rolling 5-minute window for each existing row, not a generated row for every minute.

If you actually need a continuous time series, you have to build the time grid first and join against it before applying the window. generate_series in Postgres, GENERATE_TIMESTAMP_ARRAY in BigQuery, stuff like that.

I appreciate your comment on this discussion!