How would you calculate a 7-day moving average of daily sales, and what frame clause is needed?

How to think about it

What the question is really testing

Interviewers want to know if you understand window function frame clauses — not just that AVG() OVER (ORDER BY ...) exists. The frame clause is the subtle part: it controls which rows feed the average. Get it wrong by one row and your “7-day” average is actually an 8-day one.

Step 1 — understand ROWS vs RANGE

A window function computes over a “frame” of rows. Two frame modes matter here:

• ROWS counts physical rows before/after the current row, ignoring the actual values in the ORDER BY column.
• RANGE counts rows whose ORDER BY value falls within a range from the current row’s value.

For a 7-day moving average, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means: “give me the 6 rows before this one, plus this row — 7 rows total.” It works even if some dates are missing from the table.

Step 2 — write the query

SELECT
  sale_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7d
FROM daily_sales
ORDER BY sale_date;

6 PRECEDING not 7 PRECEDING — because “6 before” plus the current row is 7 total.

Step 3 — try it live

The playground below has 10 days of sales. Watch how the moving average smooths out the spike on day 5, and notice the “ramp-up” effect in the first 6 rows where the window is still filling up.

The key insight — why ROWS not RANGE

RANGE with an interval (RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) does something subtly different: it includes all rows whose date falls within the last 6 calendar days. If your data has gaps — say, no sales on weekends — those missing dates shrink the average’s denominator unpredictably. ROWS is deterministic: it always uses exactly N physical rows.

For partitioned moving averages (e.g., per product), just add PARTITION BY:

AVG(daily_revenue) OVER (
  PARTITION BY product_id
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

The partition boundary resets the window — rows from a different product never bleed into another product’s average.