Itzik Ben-Gan wrote a whole book just on SQL windows functions and it's brilliant. He writes for SQL server usually, but he made this one more generic, he had t

Itzik Ben-Gan wrote a whole book just on SQL windows functions and it's brilliant. He writes for SQL server usually, but he made this one more generic, he had to because at the time SQL server 2012 was missing much of the functionality he described, unlike Postgres or Oracle. Postgres has incredible capability here - Window AND Frame partitions/ordering, also row based ranges or value based ranges. And as a huge bonus unique to Postgres, the wondeful - Filter by clause - so much more elegant than sub queries.