How would you add a column that calculates the rolling average of revenue per transaction timestamp, given that you only have a transaction timestamp column and no order date?

To add a column that calculates the rolling average of revenue per transaction timestamp, you can use a window function. Since you only have a transaction timestamp column, you can treat each row as a day (or aggregate by day if you have multiple rows per day). The window clause ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives you the last 7 rows, which corresponds to a 7‑day window if you have one row per day. If you have multiple rows per day, first aggregate by date, then apply the window. The query looks like:

SELECT

transaction_ts,

revenue,

AVG(revenue) OVER (ORDER BY transaction_ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d

FROM sales;

If you need the average of the sum of revenue per day, you would first GROUP BY DATE(transaction_ts) and then apply the window. The time complexity is O(n) and the space complexity is O(1) for the window frame. This approach works in most SQL engines that support window functions such as PostgreSQL, Snowflake, BigQuery, and Redshift.