The dashboard says “seven-day average.” The query says “last seven rows.” Those are only equivalent when every day has exactly one row. A missing weekend can quietly turn your weekly metric into something else.

Here is a small dataset where three reasonable-looking queries produce 42, 56, and 40. Each number has a different denominator. The goal is to choose the one that answers your question.

Explore the idea

Three denominators. Three different answers.

Compare the windows ending January 11. Outlined gaps have no source record.

42

294 orders ÷ 7 observations

Seven observations reach all the way back to January 2

ROWS counts records. Missing dates do not occupy a place in the frame.

Notice what is absent

We have eight daily order counts spanning January 1–11. January 2 is a recorded zero. January 3, 6, and 7 have no row at all.

daily_orders8 rows

Daily counts with gaps

January 2 is a recorded zero. January 3, 6, and 7 have no source row; deciding what those gaps mean is part of the lesson.

iddayorders
12026-01-0170
22026-01-020
32026-01-0414
42026-01-0528
52026-01-0842
62026-01-0956
72026-01-1070
82026-01-1184

Assume this table should contain at most one row per day. If the source contains multiple regions or products, aggregate to one row per day first, or partition the window by the entity you want to measure. Duplicate dates otherwise change the calculation.

Rows measure observations; ranges measure distance

Compare the two frames at January 11:

Compare seven observations with a seven-day interval

WITH moving AS (
 SELECT day, orders,
   round(avg(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS last_7_rows,
   round(avg(orders) OVER (ORDER BY day RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW), 2) AS observed_7_days
 FROM daily_orders
) SELECT * FROM moving WHERE day = DATE '2026-01-11';
Expected result · sample data1 row
day orders last_7_rows observed_7_days
2026-01-11 84 42 56

Scroll for more columns →

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row and six preceding observations, reaching back to January 2. Those values sum to 294, so the result is 42.

RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW covers January 5–11. The five recorded counts sum to 280, giving 56. The interval is seven calendar dates, but avg still divides by five observed, non-NULL values. A range frame does not create missing rows. DuckDB documents both frame types here.

The date filter is outside the CTE intentionally. Filtering to January 11 before calculating the windows would remove their history.

Build the calendar, then decide what a gap means

generate_series provides every date in the desired interval. Joining it to the source exposes the gaps:

Show every calendar date, with the zero-fill assumption explicit

SELECT c.day, o.orders AS recorded_orders,
  coalesce(o.orders, 0) AS orders_if_complete
FROM calendar_days c LEFT JOIN daily_orders o USING(day)
WHERE c.day BETWEEN DATE '2026-01-05' AND DATE '2026-01-11'
ORDER BY c.day;
Expected result · sample data7 rows
day recorded_orders orders_if_complete
2026-01-05 28 28
2026-01-06 NULL 0
2026-01-07 NULL 0
2026-01-08 42 42
2026-01-09 56 56
2026-01-10 70 70
2026-01-11 84 84

The setup creates calendar_days for January 1–11. The result above keeps the original NULL alongside a possible zero-filled interpretation.

Zero-fill is justified only when you know the event source is complete and an absent date means no orders. If a collector failed, that date is unknown. Turning an outage into zero orders creates a false business decline.

Our next query adopts the complete-source assumption. The complete_days view coalesces absent counts to zero; it is a policy choice you can change, not a repair that SQL can infer.

Require a full window

Report only full seven-day windows

WITH moving AS (
 SELECT day, count(*) OVER w AS days_in_frame,
   round(avg(orders) OVER w, 2) AS daily_average
 FROM complete_days
 WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
) SELECT day, days_in_frame, daily_average FROM moving
WHERE day = DATE '2026-01-11' AND days_in_frame = 7;
Expected result · sample data1 row
day days_in_frame daily_average
2026-01-11 7 40

January 5–11 now has seven rows, totaling 280 orders, so the daily average is 40. Requiring seven dates prevents the first few days of a dataset from appearing as full weekly windows.

If missing counts must remain unknown, leave them NULL and require both seven calendar rows and seven non-NULL observations before reporting an average. count(*) alone checks calendar coverage, not measurement completeness.

Your turn · check your answer

Find missing dates without confusing them with zero

Return day for calendar dates with no source row, across January 1–11. Keep the recorded zero on January 2 out of the result.

See the expected result
Your target result3 rows
day
2026-01-03
2026-01-06
2026-01-07
Sample data is loaded for you.

Preparing the editor…

Need a hint?

Test the joined source key for NULL before coalescing the count.

Show a solution
SELECT c.day FROM calendar_days c LEFT JOIN daily_orders o USING(day) WHERE o.id IS NULL ORDER BY c.day;

Name the metric honestly

“Average per observed day” and “average per calendar day” are both useful. They are not interchangeable. Keep a count of observed days beside the metric when missingness matters, and document which time zone defines a day before aggregating timestamped events.

Try changing January 2’s recorded zero to NULL, then rerun the first example. You will see that avg ignores an unknown value but includes a known zero. That small distinction is often the explanation for an unexpectedly optimistic chart.