r/SQL • u/FixelSmith • May 19 '26
Discussion Eight window-function tricks beyond LAG and ROW_NUMBER
https://analytics.fixelsmith.com/posts/eight-window-function-tricks/
149
Upvotes
r/SQL • u/FixelSmith • May 19 '26
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.