SQL & DatamediumSign in to track progress

Rolling 7-Day Average Revenue

Given daily_revenue(day DATE, amount NUMBER) with at most one row per day, return every day in the table's range with the average revenue over that day and the six days before it. Days with no sales count as zero revenue, so a missing day must not shrink the window.

Write the query, then say what changes if the table is guaranteed to have one row per day.

Hints

Hint 1

AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) averages the previous six rows, which is only the previous six days when no day is missing.

Hint 2

Generate the calendar (a date spine from the minimum to the maximum day), left-join the revenue onto it with COALESCE(amount, 0), then apply the row-based frame.

Hint 3

A self-join ON r2.day BETWEEN r.day - 6 AND r.day with SUM(r2.amount) / 7 is the window-free version and also handles gaps.

Solution

Show the approach

Approach

Window frames count rows, not days. If a day is missing, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW silently reaches further back in time. Build the calendar first so every day has a row, treat missing revenue as zero, and then the row frame is exactly a seven-day frame.

WITH bounds AS (
  SELECT MIN(day) AS first_day, MAX(day) AS last_day FROM daily_revenue
),
calendar AS (
  SELECT DATEADD(day, seq4(), first_day) AS day
  FROM bounds, TABLE(GENERATOR(ROWCOUNT => 100000))
  QUALIFY day <= last_day
),
filled AS (
  SELECT c.day, COALESCE(r.amount, 0) AS amount
  FROM calendar c
  LEFT JOIN daily_revenue r ON r.day = c.day
)
SELECT day,
       AVG(amount) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM filled
ORDER BY day;

In Postgres the spine is generate_series(first_day, last_day, interval '1 day'), and a RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW frame on the raw table also works — but it still divides by the number of rows present, so a gap raises the average; the filled calendar is the version that treats a missing day as zero.

Worth saying out loud

The first six days average over fewer than seven rows; say whether those partial windows should be reported, divided by seven, or dropped.
With one row per day guaranteed, the CTEs disappear and the window alone is the answer — that guarantee is the question to ask first.
SUM(amount) OVER (...) / 7 and AVG differ exactly on the partial windows and on how zeros are counted; pick on purpose.

Asked at

Snowflake