Analytics Patterns · RANGE window frame · calendar moving average
52. 7-day moving average revenue
Concept: RANGE window frame · calendar moving averageshow
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means 'the last 7 rows', which covers more than a week when some days have no data. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW covers the last 7 calendar days instead. Divide the window's SUM by 7 so days without sales count as zero.
Example (different data):
SELECT day, rides,
SUM(rides) OVER (ORDER BY day
RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) / 3.0 AS avg_3d
FROM daily_rides;Your task
The ops dashboard smooths daily revenue with a 7-day moving average, but July has days with no completed orders.
Total completed order amounts per order_date. Then, for each order_date from 2024-07-07 onward, return day, revenue (that day's completed total) and moving_avg_7d (completed revenue over the 7 calendar days ending that day, divided by 7, rounded to 2 decimals). Oldest first.
Starting Postgres in your browser…
Checking your access…