Moving averages and window frames

You know LAG, LEAD, and running totals. Now let us narrow the window frame to build moving averages, understand ROWS vs RANGE, and put it all together.

A moving average narrows the frame to a fixed number of preceding rows. This smooths out fluctuations and reveals underlying trends.

moving_average.sql
sql
-- 3-month moving average
WITH monthly_rev AS (
  SELECT
    DATE_TRUNC('month', o.order_purchase_timestamp) AS month,
    SUM(oi.price) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY DATE_TRUNC('month', o.order_purchase_timestamp)
)
SELECT
  month,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::NUMERIC, 2
  ) AS moving_avg_3m
FROM monthly_rev
ORDER BY month;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW creates a sliding window of 3 rows. The average smooths out monthly fluctuations.

Window frame options

ROWS counts physical rows (2 PRECEDING = the 2 rows before this one). RANGE considers logical values (RANGE BETWEEN INTERVAL '7 days' PRECEDING means all rows within 7 days). ROWS is more common and predictable. Use RANGE when your data has gaps (missing months) and you want a time-based window. One gotcha worth memorizing: when an OVER clause has ORDER BY but no explicit frame, PostgreSQL silently applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as the default.

Fill in the blanks: Complete the lag query

Loading practice…

Code playground: Monthly dashboard

Loading practice…

Timed quiz: Window functions speed round

Loading practice…