Calculate Rolling 7-Day Moving Sums in SQL

Question

Given a `customer_orders` table (customer_id, visited_on, amount), calculate the 7-day rolling sum and 7-day moving average of amount for each day starting from the 7th day onward, rounded to 2 decimal places.

Accepted Answer

Optimal Solution:
WITH daily_totals AS (
  SELECT 
    visited_on, 
    SUM(amount) AS daily_amount
  FROM customer_orders
  GROUP BY visited_on
),
rolling_calcs AS (
  SELECT 
    visited_on,
    SUM(daily_amount) OVER (
      ORDER BY visited_on 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS amount,
    ROUND(
      AVG(daily_amount) OVER (
        ORDER BY visited_on 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
      ), 
      2
    ) AS average_amount,
    ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
  FROM daily_totals
)
SELECT visited_on, amount, average_amount
FROM rolling_calcs
WHERE rn >= 7
ORDER BY visited_on ASC;

Technical Explanation:
Aggregate daily totals first. Then use `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` to compute the 7-day sum and average. Filter `rn >= 7` so incomplete leading windows under 7 days are excluded.

visited_on	amount
2026-01-01	100
2026-01-02	110
2026-01-03	120
2026-01-04	130
2026-01-05	110
2026-01-06	140
2026-01-07	150
2026-01-08	80

visited_on	amount	average_amount
2026-01-07	860	122.86
2026-01-08	840	120