Compute a Rolling Moving Average With a Window Frame

Smooth time-series data by computing a rolling average over a fixed window of preceding rows using `AVG … OVER (ORDER BY … ROWS BETWEEN n PRECEDING AND CURRENT ROW)`.

Created May 5, 2026 Last updated May 5, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Compute a 3-day rolling average of daily revenue

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a frame that includes at most the current row and two rows before it in date order. Jan 1 has no preceding rows so the average is computed over just itself: 10. Jan 2 has one preceding row so the average spans [10, 20] = 15. From Jan 3 onward the full 3-row window is available: [10, 20, 30] = 20, [20, 30, 40] = 30, [30, 40, 50] = 40. Multiplying revenue * 1.0 forces floating-point division in all engines. To compute a 7-day moving average, change 2 PRECEDING to 6 PRECEDING.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE daily_sales (sale_date VARCHAR(10), revenue INT);

INSERT INTO
  daily_sales
VALUES
  ('2024-01-01', 10),
  ('2024-01-02', 20),
  ('2024-01-03', 30),
  ('2024-01-04', 40),
  ('2024-01-05', 50);

Validated query Shared across supported engines.

SQL

SELECT
  sale_date,
  revenue,
  AVG(revenue * 1.0) OVER (
    ORDER BY
      sale_date ROWS BETWEEN 2 PRECEDING
      AND CURRENT ROW
  ) AS moving_avg_3day
FROM
  daily_sales
ORDER BY
  sale_date;

Expected result Returned rows for the shared example.

sale_date	revenue	moving_avg_3day
2024-01-01	10	10
2024-01-02	20	15
2024-01-03	30	20
2024-01-04	40	30
2024-01-05	50	40

Output is identical across all supported engine versions.

Useful when

Where this command helps.

smoothing daily sales data to reveal an underlying trend by dampening day-to-day noise
computing a 7-day rolling average of active users to feed a dashboard trend line

Explanation

What the command is doing.

A moving (or rolling) average replaces each row's value with the average of that row and a fixed number of preceding rows. This dampens short-term noise and makes trends easier to see. The SQL idiom uses AVG(column) OVER (ORDER BY time_col ROWS BETWEEN n PRECEDING AND CURRENT ROW), where n PRECEDING controls the lookback size. Unlike a centred moving average (which also looks forward), this variant is fully causal — each output depends only on past and current data, making it safe for live dashboards. At the start of the series where fewer than n prior rows exist, the frame automatically shrinks and the average is computed over however many rows are available. To reset the average for each group (e.g. per product), add PARTITION BY group_col before ORDER BY. All engines that support window functions implement ROWS BETWEEN frame syntax.

Compute a Rolling Moving Average With a Window Frame

Compute a 3-day rolling average of daily revenue

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.