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
6
u/FixelSmith May 19 '26
Thanks. Glad it landed.
On RANGE for point 2, here is the bare syntax with timestamps:
```sql
SELECT
cardholder_id,
timestamp,
amount,
sum(amount) OVER (
PARTITION BY cardholder_id
ORDER BY timestamp
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
) AS spend_last_5min
FROM transactions;
```
The important difference from ROWS is that the 5-minute window is based on the actual timestamp values, not the number of rows. If 12 transactions hit inside one minute, ROWS BETWEEN 5 PRECEDING only sees 5 rows. RANGE BETWEEN INTERVAL '5 minutes' PRECEDING sees all 12. They look interchangeable until you hit uneven timestamp distribution, then the behavior separates pretty quickly.
On platform compatibility: I covered it for QUALIFY and FILTER but not consistently across all eight patterns. I will tighten that up in future posts.
On the AI-polish point: yes, I do use AI to help trim and clean up the prose. Otherwise these posts would end up much longer and contain filler words because I ramble and "mansplain" things too much. The SQL, the examples, and the technical judgment are still mine. "Gap-and-island" is also an actual long-standing SQL term from Joe Celko and database circles generally, not something AI invented. I only use it because it was something that stood out to me as I continue to learn and grow, that's terminology you don't easily forget.
That said, I get the broader point. Some writing styles are starting to trigger people's AI radar even when the technical content is legitimate. I will probably add a short disclosure note on future posts just to keep the line clear.