@Simon_Taranto, I liked the post. I think users unfamiliar with windowing might try to do this work in Ruby instead of PG, which would be a performance nightmare on a large data set. Question for you: have you tried to execute window functions in PG using Dockyard’s pg-ext gem? Dockyard has written a blog post about supporting windowing and CTE and done some slide decks on it, but actual documentation is thin.
My app uses windowing on several important queries and I would love to get this code into ActiveRecord or at least Arel so that I don’t have huge chunks of SQL literals in my code (and could potentially reuse some pieces between similar queries), but given the quasi-undocumented state of this particular piece of pg-ext I’m concerned about how production worthy the features are. I don’t know if anyone outside Dockyard is using windowing/CTE portions yet.
For curious readers, here’s a slide deck by the author that has some other PG techniques: