DEV Community

Discussion on: #SQL30 Day 2: Stock Price Variance

Collapse
 
helenanders26 profile image
Helen Anderson • Edited

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

select
 day,
 close,
 lag(close) over() as last_day,
 close - lag(close) over() as variance,
 date_trunc('month',day)::date as month,
 avg(close) over(partition by date_trunc('month',day)) as monthly_avg
from day2.stockprice
 order by day
Collapse
 
helenanders26 profile image
Helen Anderson

Collapse
 
zchtodd profile image
zchtodd

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.

Thread Thread
 
helenanders26 profile image
Helen Anderson

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/