IntermediateWindow Functions

7-day moving average of daily sales

The query

SQL

WITH daily AS (
  SELECT
    order_date::DATE AS day,
    SUM(total) AS daily_revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY order_date::DATE
)
SELECT
  day,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7d
FROM daily
ORDER BY day;

Tested against PostgreSQL 16

Note

“7-day moving average smooths spikes in daily revenue. ROWS BETWEEN 6 PRECEDING = window of 7 days.

Tables referenced

orders6 cols