Almost identical to what I ended up doing, with the exception that I did the month extraction inside of a CTE. Using a CTE to avoid repetition of an expression might sometimes be a bad idea, especially with PG, where I believe all CTEs are materialized. So I probably paid dearly for that little convenience!
I hear though that PG 12 has made some improvements in regards to CTE performance.
I've been travelling this weekend so late to the game.
Here's my answer before I go check out day three and to see if we get the same
Almost identical to what I ended up doing, with the exception that I did the month extraction inside of a CTE. Using a CTE to avoid repetition of an expression might sometimes be a bad idea, especially with PG, where I believe all CTEs are materialized. So I probably paid dearly for that little convenience!
I hear though that PG 12 has made some improvements in regards to CTE performance.
Yes! I am very excited to be able to use CTEs without fear. The release notes for PG12 are out now - postgresql.org/about/news/1976/