IntermediateWindow Functions

Moving average: 3-month rolling average revenue

The query

SQL

SELECT
  month,
  monthly_rev,
  ROUND(AVG(monthly_rev) OVER (
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_3m
FROM (
  SELECT
    DATE_TRUNC('month', order_date)::DATE AS month,
    SUM(total) AS monthly_rev
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
) monthly
ORDER BY month;

Tested against PostgreSQL 16

Note

“ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = 3-row window. RANGE vs ROWS: different boundary types.

Tables referenced

orders6 cols