Frames and moving average.

A frame tells which rows of the window count for the current row. The explicit form ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row and the two before it. So AVG(total) OVER (ORDER BY order_date, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is a moving average of the last 3 ROWS.

ROWS counts rows, not calendar days. If a day has no orders the frame does not "know" about it; it simply takes the previous rows. A true "last 3 days" needs other techniques that we do not cover here. At the start the frame is incomplete — the first row has only itself, the second only two rows. That is normal; COUNT(*) OVER (...) with the same frame shows how many rows really took part.

Other bounds: UNBOUNDED PRECEDING (from the start), n FOLLOWING (forward), CURRENT ROW. Always use a unique tie-breaker in the ORDER BY of OVER and a final ORDER BY.

Common mistakes: reading ROWS as calendar days; a missing tie-breaker; expecting a full frame on the first rows; forgetting rounding.

Small example:

SELECT id, total, SUM(total) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS last_two FROM orders ORDER BY id;