[hospadar]

Wow I love me a window function!

Favorite/weirdest thing I learned to do most recently with window functions is using/abusing min() and max(). If there is no value in a column in a particular window, min and max will give NULL.

We use this to do really interesting stuff (entirely in sql!) like "partition usage into sessions where a session is defined as a bunch of activity by a user where they did an action at least once every 30 minutes"

[marcosdumay]

SQL could really use some standard aggregation function that returns null for no rows, the value of a single row and error if there is more than one row.

That kind of query happens all the time, and min/max behavior isn't the safest one.