This query generates the moving average of sales in three consecutive months. The size of the window frame is three rows: two preceding rows plus the current row. The window slides from the beginning to the end of the partition.
SELECT prod_id, month_num, sales, AVG(sales) OVER (PARTITION BY prod_id ORDER BY month_num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM sale WHERE rep_id = 1 ORDER BY prod_id, month_num;
prod_id month_num sales avg(sales) ------- --------- ------ ---------- 10 1 100 100.00 10 2 120 110.00 10 3 100 106.66 10 4 130 116.66 10 5 120 116.66 10 6 110 120.00 20 1 20 20.00 20 2 30 25.00 20 3 25 25.00 20 4 30 28.33 20 5 31 28.66 20 6 20 27.00 30 1 10 10.00 30 2 11 10.50 30 3 12 11.00 30 4 1 8.00