Mastering SQL Window Functions for Analytics

Window functions are the Swiss Army knife of SQL analytics.

Frame Specifications

Understanding ROWS BETWEEN vs RANGE BETWEEN is crucial for rolling averages.

SELECT
  date,
  price,
  AVG(price) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as 7_day_moving_avg
FROM stock_prices;