https://www.amitgrinson.com/blog/window-function-with-groupby/

[tiniacoleyba]

Nice post about the relationship between SQL GROUP BY and window functions. I liked your explanation but agree that most people, including myself (before this post), won't understand the syntax, and it is probably more convenient to use CTEs. I encountered this convoluted syntax in a work repository of analytics queries and couldn't make sense of it at first. That's how I arrived at your post. It ranks very well on search engines. Good work!

Sidenote: the Utterances tool scared me away from commenting at first. I don't want to let it access my GH account so I tried opening a issue instead to see if it is linked to the comments in your post.

[AmitLevinson]

Hey, Thank you for this! Always glad to hear the value readers get from my posts as well as discovering it serendipitously.

At the time of writing the post I also found it a little weird, today I use it more often so I'm actually more inclined to use it within the same query. Also due to it being faster than wrapping the whole query in a CTE to only run something afterwards (in terms of writing, not necessarily performance). Now that I also use BigQuery more it's pretty crazy that you can use the idea of window function after group by and you can then use it within QUALIFY. I think that's an overkill but shows to what extent it can be leveraged.