sql

🔷 SQL: Rolling Averages

-- 7-day rolling average of daily revenue
SELECT date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_revenue;

Key detail: 6 PRECEDING + CURRENT ROW = 7 rows total. A common mistake is writing 7 PRECEDING which gives 8 rows.

Running total (cumulative sum):

SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)