Quick answer: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means seven rows, not necessarily seven days. For a daily moving average, first create exactly one row per calendar date. Fill absent sales with zero only when you know the source is complete, then calculate the window and suppress incomplete starting frames.

Why can a seven-row average be wrong?

Consider a synthetic US store reporting revenue in USD. September 1 has $700 in sales; September 2 has $100; September 3 has no sales; September 4 has $200; and September 5–8 each have $100. The export omits dates with no sales.

On September 8, the seven available rows cover eight calendar days. Averaging those rows gives $200 because the older $700 day remains included. The correct September 2–8 total is $700, so the seven-calendar-day average is $100. This is a window-definition error, not a rounding issue.

Run the complete SQLite example

This self-contained query uses synthetic daily sales. Assume the export is complete through September 8 and that an absent date means no revenue. Dates have already been assigned to the store’s reporting timezone. The calendar supplies the missing September 3 row; aggregation ensures one row per date.

WITH RECURSIVE calendar(day) AS (
 SELECT '2026-09-01'
 UNION ALL
 SELECT date(day, '+1 day') FROM calendar
 WHERE day < '2026-09-08'
), sales(day, usd) AS (
 VALUES ('2026-09-01',700),('2026-09-02',100),
 ('2026-09-04',200),('2026-09-05',100),
 ('2026-09-06',100),('2026-09-07',100),
 ('2026-09-08',100)
), daily AS (
 SELECT c.day, COALESCE(SUM(s.usd),0) AS usd
 FROM calendar c LEFT JOIN sales s ON s.day=c.day
 GROUP BY c.day
), rolling AS (
 SELECT day, usd,
 COUNT(*) OVER w AS days_in_frame,
 AVG(usd) OVER w AS average_usd
 FROM daily
 WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
)
SELECT day, usd, ROUND(average_usd,2) AS seven_day_average_usd
FROM rolling WHERE days_in_frame=7 ORDER BY day;

Check the expected result

day         usd   seven_day_average_usd
2026-09-07  100   185.71
2026-09-08  100   100.00

The first window contains September 1–7: $1,300 divided by seven is approximately $185.71. The next window drops September 1 and adds September 8, producing $700 divided by seven. Earlier dates are omitted because they do not yet have seven calendar rows.

Notice that the final date filter belongs outside the window calculation. If you filter the input down to September 8 before computing the window, the previous six days are unavailable. For a production report starting September 8, load history from at least September 2 before applying the display range.

What if the missing date is an outage?

Do not use the zero-fill assumption when September 3 is missing because ingestion failed. Keep a separate completeness flag for each expected reporting date. Require seven complete dates before showing a seven-day result, or clearly label the metric unavailable. Counting calendar rows proves the frame has seven dates; it does not prove all seven source feeds arrived.

Averaging six known days while silently excluding an unknown day changes the denominator. Calling that result a seven-day average would hide uncertainty. A useful dashboard exposes both the reporting cutoff and whether the period is complete.

Adapt this for US and UK reporting

Assign transactions to the agreed business date before aggregation. A US store and a UK store can put the same UTC instant on different local dates. For separate stores, build one calendar per store and use PARTITION BY store_id in the window. Keep currencies separate unless an explicit exchange-rate rule converts them; the USD amounts here are illustrative.

For date-boundary examples, see daily users across reporting timezones. For user metrics, a daily moving average is also different from weekly distinct users.

Explain your checks in an interview

State the row grain, timezone, completeness rule and denominator before presenting the SQL. Verify the missing-day case manually, check that duplicate dates are aggregated, and confirm that the initial six partial windows are withheld. If the business wants seven trading days instead of seven calendar days, define that calendar explicitly.

Continue with window-function exercises or a revenue-drop investigation.

Source: SQLite window-function documentation defines ROWS frames and window ordering. The sales data and worked calculation above are original synthetic learning examples; they are not employer interview questions.