Level 5 · 7-day moving average of orders

A moving average smooths noisy daily numbers by averaging each day with its neighbors. Day-of-week swings and one-off spikes flatten out, and the trend becomes visible. Operations, marketing and product teams use 7-day averages constantly, because a 7-day window always contains exactly one of each weekday.

The frame clause

A window frame says exactly which rows around the current one the function should see:

AVG(orders) OVER (
  ORDER BY order_day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS orders_7d_avg

6 PRECEDING AND CURRENT ROW is seven rows: today and the six before. Other frames you will use:

frame	meaning
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	running total or running average
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING	centered 7-row window
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	the whole partition

Compute before you filter

Kevin wants December only, but December 1st’s average needs November 25 to 30. If you filtered the daily CTE to December first, the window would have nothing before December 1st, and the first six days would be averages of 1 to 6 days instead of 7. As with LAG, compute the window on the full data, then filter in the outer query.

When you do report the first days of a series with no earlier data, they are averages of fewer than seven days. Either drop them or count the rows in the frame (COUNT(*) OVER (same window)) and show NULL when it is below 7.

ROWS counts rows, not days

6 PRECEDING means six rows back. If a day had zero orders, it has no row in daily, and the window silently reaches back eight calendar days. Harbor & Pine has orders every day, so it is fine here. In sparse data (one product, one city), generate a full calendar and LEFT JOIN onto it with COALESCE(orders, 0) before applying the window.

Postgres 11+, DuckDB and MySQL 8 also allow a RANGE frame with an interval, which uses the values instead of row positions:

AVG(orders) OVER (
  ORDER BY order_day
  RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
)

Postgres writes INTERVAL '6 days'. BigQuery only allows RANGE over numbers, so you order by UNIX_DATE(order_day) and write RANGE BETWEEN 6 PRECEDING AND CURRENT ROW. This handles gaps in the dates, but a missing day still does not count as zero in the average: it is just absent. The calendar join is the only way to average real zeros.

Moving sum vs moving average

A 7-day moving sum (SUM(orders) OVER (... 6 PRECEDING ...)) answers “orders in the last 7 days”, the basis of metrics like weekly active users computed daily. The frame is the same; only the function changes.

Common mistakes

• Writing RANGE 6 PRECEDING on a date column without an interval, which errors or means something different by dialect.
• Using ROWS BETWEEN 7 PRECEDING AND CURRENT ROW, which is 8 days.
• Filtering before the window, as above.