How would you modify your query to compute a 30‑day rolling average of daily revenue when you only have a transaction timestamp column and no order date?

If you only have a transaction timestamp, you can derive the date using DATE_TRUNC('day', transaction_ts) or CAST(transaction_ts AS DATE). First aggregate revenue per day, then apply a 30‑day window. For example:

WITH daily AS (

SELECT DATE_TRUNC('day', transaction_ts) AS day,

     SUM(revenue) AS daily_rev

FROM sales

GROUP BY DATE_TRUNC('day', transaction_ts)

)

SELECT

day,

daily_rev,

AVG(daily_rev) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_30d

FROM daily;

The window frame ROWS BETWEEN 29 PRECEDING AND CURRENT ROW covers the current day plus the previous 29 days, giving a 30‑day rolling average. This approach works in engines that support window functions and date truncation, such as PostgreSQL, Snowflake, BigQuery, and Redshift. The time complexity is linear in the number of days, and the space complexity is constant for the window frame.