Write an SQL query to compute a 7‑day rolling average of daily revenue using a window function.

Here is a concise query that computes a 7‑day rolling average of daily revenue:

WITH daily AS (

SELECT DATE(transaction_ts) AS day,

     SUM(revenue) AS daily_rev

FROM sales

GROUP BY DATE(transaction_ts)

)

SELECT

day,

daily_rev,

AVG(daily_rev) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d

FROM daily

ORDER BY day;

Explanation:

• The CTE aggregates revenue per calendar day.
• The window function AVG(...) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calculates the average of the current day and the six preceding days, giving a 7‑day rolling average.
• The query runs in O(n) time and uses O(1) additional space for the window frame. It works in PostgreSQL, Snowflake, BigQuery, Redshift, and other SQL engines that support window functions.