Level 9 · Three-month trend per category

A moving average replaces each value with the average of a small window around it, which smooths out noise while keeping the trend. The window frame says exactly which rows are included.

Recap: the frame

AVG(net_revenue) OVER (
  PARTITION BY category
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

• PARTITION BY category: each category is averaged on its own.
• ORDER BY month: “preceding” means earlier months.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: this row and the two before it, three months in total. For a longer window, change the 2 (11 PRECEDING gives a 12-month average).

ROWS counts rows, not months, so it is only a true three-month window when no month is missing. Every category here has all 36 months from 2023 to 2025; with gaps you would join a calendar spine first.

Filter after the window

Windows only see the rows left after WHERE. Here is Outdoor, computed both ways:

month	net_revenue	3m avg (correct)	3m avg (filtered first)
2025-01	21,520.57	21,158.42	21,520.57
2025-02	7,369.74	16,602.84	14,445.16
2025-03	38,755.54	22,548.62	22,548.62

From March on the two agree, because the window no longer reaches back into 2024. The first two months are quietly wrong when the filter runs first. So: compute the window in a CTE on full history, then keep 2025 in the outer query.

The table also shows why merchandising wants smoothing: Outdoor revenue fell to $7,369.74 in February 2025 and then jumped to $38,755.54 in March as the season began.

Common mistakes

• No PARTITION BY. January 2025 of Furniture would average in the last months of Decor.
• RANGE instead of ROWS with duplicate months gives different frames; ROWS is the predictable choice.
• Rounding before averaging. Round only the final output.

In SQL Server

The same frame syntax works: AVG(net_revenue) OVER (PARTITION BY category ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Note that AVG of an integer column returns an integer in SQL Server, so cast to decimal first when averaging counts.