Discussion on: #SQL30 Day 2: Stock Price Variance

View post

I ended up doing the same as Helen and the author. for variety, here's a variant that uses CTEs but EXPLAIN ANALYSE says it's slightly (10%) slower:

WITH monthly AS (
  SELECT date_trunc('month', day) as month, avg(close) AS month_close
  FROM stockprice
  GROUP BY 1
)
SELECT sp.day, sp.close, LAG(sp.close) OVER () AS last_close,
  close - LAG(sp.close) OVER () AS diff_close,
  m.month_close
FROM stockprice sp JOIN monthly m ON date_trunc('month', sp.day) = m.month
ORDER BY sp.day

being from a stats background I found your use of the term "variance" somewhat confusing, I thought you were after something more than just subtraction.