Rolling average in SQL (and the missing-day trap)

Why the usual answer is wrong

SELECT day, ROUND(avg_3, 2) AS avg_3_days
FROM (
  SELECT day,
    AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3
  FROM daily_sales
)
WHERE day >= '2026-03-03'
ORDER BY day;

4 March has no row, so for 5 March the window takes 2, 3 and 5 March: four calendar days, and the empty day isn't counted as 0.

The answer

SELECT d.day,
  ROUND((SELECT SUM(x.amount) FROM daily_sales x
         WHERE x.day BETWEEN date(d.day, '-2 day') AND d.day) / 3.0, 2) AS avg_3_days
FROM daily_sales d
WHERE d.day >= '2026-03-03'
ORDER BY d.day;

ROWS counts rows, not days. Add up the sales whose date falls in the last 3 calendar days and divide by 3, so a missing day counts as 0.

In real work you'd join to a calendar table so every day has a row. In PostgreSQL, a window with RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW works on dates.