Rolling 7-day average

Use an average with a frame of the current row and the 6 rows before it. But that frame counts rows, not days. If a day with no orders has no row, "6 rows back" reaches further than 6 days, so you must first make sure every date has a row.

The window itself

SELECT order_date,
       orders,
       AVG(orders) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d
FROM daily_orders;

Why missing days break it

Say the table has rows for Mon, Tue, Thu, Fri (Wednesday had no orders so there is no row). The frame "6 preceding rows" now covers more than a week of calendar time, and the average is taken over fewer than 7 days. The number looks fine and is wrong.

Fix 1: fill the gaps with a date spine

WITH spine AS (
  SELECT d AS order_date
  FROM generate_series(DATE '2025-01-01', DATE '2025-03-31', INTERVAL '1 day') AS g(d)
)
SELECT s.order_date,
       COALESCE(o.orders, 0) AS orders,
       AVG(COALESCE(o.orders, 0)) OVER (
         ORDER BY s.order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d
FROM spine s
LEFT JOIN daily_orders o ON o.order_date = s.order_date;

Now each row is one calendar day, so 6 rows back means 6 days back. Generating the spine differs per engine: generate_series in Postgres, GENERATE_DATE_ARRAY in BigQuery, or a dim_date table.

Fix 2: a range frame by interval

Some engines let you write RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW, which measures the frame in days. Postgres supports this. Support varies elsewhere, so check yours. The date spine is portable.

The first six days

For the first week, the window holds fewer than 7 days. You can show the partial average, or return NULL until a full window exists:

CASE WHEN ROW_NUMBER() OVER (ORDER BY s.order_date) >= 7 THEN avg_7d END

Say which one you chose. A chart that starts with partial averages makes the first days look more volatile than they are.