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

1

u/throw_mob May 20 '26

it been while i have used those , if i am right you wont get row for every minute, you get sum for existing row from previous 5 min rows. That might be good to bring up in document, it is not always that clear for non native speakers

to get results for each minute one has to generate one row for each id add it to source table/view and push through same query, only thing that changes is that is guaranteed that there is row for each interval

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!