Compute a 7-day trailing moving average of daily revenue
Table daily_revenue(day, revenue) has one row per calendar day with no gaps. Compute a trailing 7-day moving average of revenue — each day averaged together with the six days before it.
-- 7-day trailing moving average of daily revenueWrite the query.
Use an explicit frame: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The current row plus the six before it form a 7-row trailing window. ROWS counts rows, so it needs one row per day.
- ✗Assuming the default
ORDER BYframe is a 7-day window, not cumulative - ✗Mixing up
PRECEDINGandFOLLOWINGfor a trailing window - ✗Replacing the frame with a fixed weekly
GROUP BYbucket
- →How does the answer change if some days are missing from the table?
- →Why is
ROWSpreferred overRANGEfor a fixed-count moving average?
The explicit frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW takes the current row and six before it — exactly 7 days:
SELECT day,
revenue,
AVG(revenue) OVER (ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_revenue
ORDER BY day;Without a frame, OVER (ORDER BY day) gives a cumulative average from the start, not a 7-day one. ROWS counts physical rows, so this is correct only with one row per day. If dates can be missing, use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW, which frames by the date value rather than by row count.
