SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…