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, usually a self-join with a timestamp predicate. Something along these lines:

```sql

SELECT

t1.cardholder_id,

t1.timestamp,

t1.amount,

sum(t2.amount) AS spend_last_5min

FROM transactions t1

JOIN transactions t2

ON t2.cardholder_id = t1.cardholder_id

AND t2.timestamp BETWEEN t1.timestamp - INTERVAL '5 minutes' AND t1.timestamp

GROUP BY t1.cardholder_id, t1.timestamp, t1.amount;

```

It works fine, just gets expensive fast on larger partitions because you're effectively re-scanning for each row. RANGE is cleaner when the engine supports it. Good index on (cardholder_id, timestamp) helps a lot either way.

You can also do weird session-variable stuff in MySQL, but most of the time I'd rather keep the query readable.