A rolling 3-month average smooths a monthly series: each month shows the average of itself and the two months before it. It follows a change faster than a rolling 12-month total, and still evens out a single unusual month.

A small example

The same data as in the rolling 12-month guide: one order per month from January 2025 to March 2026, with nothing sold in August 2025.

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2025-01-15', 100), ('2025-02-15', 120), ('2025-03-15', 90),
  ('2025-04-15', 110), ('2025-05-15', 130), ('2025-06-15', 100),
  ('2025-07-15', 80),  -- no sales in August
  ('2025-09-15', 120), ('2025-10-15', 140), ('2025-11-15', 150),
  ('2025-12-15', 200), ('2026-01-15', 110), ('2026-02-15', 130),
  ('2026-03-15', 100);

The query

WITH Bounds AS (
  SELECT DATE_TRUNC('month', MIN(OrderDate)) AS FirstMonth,
         DATE_TRUNC('month', MAX(OrderDate)) AS LastMonth
  FROM Sales
),
Months AS (   -- one row per calendar month, with or without sales
  SELECT DATEADD(month, f.value::INT, b.FirstMonth) AS MonthStart
  FROM Bounds b,
       LATERAL FLATTEN(input => ARRAY_GENERATE_RANGE(
         0, DATEDIFF(month, b.FirstMonth, b.LastMonth) + 1)) f
),
Monthly AS (
  SELECT DATE_TRUNC('month', OrderDate) AS MonthStart,
         SUM(Amount) AS Sales
  FROM Sales
  GROUP BY 1
),
Filled AS (
  SELECT m.MonthStart, COALESCE(s.Sales, 0) AS Sales
  FROM Months m
  LEFT JOIN Monthly s ON s.MonthStart = m.MonthStart
)
SELECT MonthStart, Sales,
       IFF(COUNT(*) OVER (ORDER BY MonthStart
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3,
           ROUND(AVG(Sales) OVER (ORDER BY MonthStart
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2),
           NULL) AS Rolling3mAvg
FROM Filled
ORDER BY MonthStart;

The CTEs are explained in the rolling 12-month guide: Months lists every calendar month and Filled gives the months without orders a 0. The window then covers three rows, which are always three calendar months. COUNT(*) over the same window returns NULL for January and February 2025, which do not have two months before them. From June to December 2025 the result is:

MonthStart Sales Rolling3mAvg
2025-06-01 100.00 113.33
2025-07-01 80.00 103.33
2025-08-01 0.00 60.00
2025-09-01 120.00 66.67
2025-10-01 140.00 86.67
2025-11-01 150.00 136.67
2025-12-01 200.00 163.33

The empty August pulls the average down for three months, August, September and October, and then drops out of the window.

Two ways to get a missing month wrong

MonthStart Sales No month list NULL instead of 0 Correct
2025-07-01 80.00 103.33 103.33 103.33
2025-08-01 0.00 no row 90.00 60.00
2025-09-01 120.00 100.00 100.00 66.67
2025-10-01 140.00 113.33 130.00 86.67
2025-11-01 150.00 136.67 136.67 136.67

For sales, a month without orders really is 0. For a measure that was simply not recorded, such as a missing meter reading, NULL and an average of the months available can be the right answer. Decide which case you are in before choosing COALESCE.

Variations

Average per day. Months have 28 to 31 days, so February’s total is lower even when daily sales are the same. Add DAY(LAST_DAY(m.MonthStart)) AS DayCount to Filled, then divide the two rolling sums:

SUM(Sales)    OVER (ORDER BY MonthStart ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
/ SUM(DayCount) OVER (ORDER BY MonthStart ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS AvgPerDay

Centred average. For analysis rather than reporting, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING centres the window on the month. The latest month then has no value until the next one is complete.

The current month. A month in progress has a partial total that pulls the average down. Stop the month list at the previous month, for example with LastMonth set to DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE())).

References