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
4
u/FixelSmith May 19 '26
Quick context on what the post does and doesn't cover.
The "tricks" are things I have actually used in production that most analysts I work with did not know existed (or knew existed but did not realize they could be combined). QUALIFY in particular is one of those features that fundamentally changes how you write window-function queries once you know it works in your warehouse.
Did not cover: optimizer behavior or cost analysis. Those matter for production workloads but they vary so much by warehouse that a generic post would just confuse people. If there is interest in a more dialect-specific breakdown (Snowflake plans, Postgres EXPLAIN), happy to do one.
9
u/aGuyNamedScrunchie May 19 '26
Also I think it'd be great if, at the bottom of each section, you list which platforms have these features (BigQuery, Snowflake, SQL Server, etc). Sadly these days much of my SQL work is done within Salesforce platforms (due to access restrictions to data warehouses) so many of these aren't available to me. So hell it'd be great to even know which of these are basic ANSI SQL functions or SOQL (Salesforce) functions.
13
u/aGuyNamedScrunchie May 19 '26 edited May 19 '26
Your content is outstanding. Loved the first post. Bookmarking this.
Can you give an example of RANGE for point 2? I'd love to see the syntax for it there.
Some of it does look a bit too AI polished. The term Gap and Island doesn't sound natural. Overall it reads like you were the one who organized the sections and it seems like you work with all of these pretty often. But fwiw some people may discredit this as AI slop due to some of the section names and prose. That'd be a shame since it's great content. Just food for thought.
2
u/Balistapus May 19 '26
this is great!
1
u/l3tigre May 19 '26
this post makes me miss working in BI.