How would you compute a 7‑day rolling average of daily revenue when there can be multiple transactions per day, using transaction timestamps?

When there are multiple transactions per day, you first need to aggregate the revenue for each day. You can do this with a subquery or a CTE that groups by the date part of the transaction timestamp:

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;

The window clause ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives the last 7 days of revenue, and the AVG function computes the average over that window. If you want the average of the sum of revenue, you can simply use the AVG as shown. This 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 engines that support window functions.