Running Totals and Moving Averages

A window function can look at a sliding range of rows, called a frame. That's how running totals and moving averages work.

This lesson uses daily_sales from the E-commerce database: one row per day with that day's revenue.

Running total, from the first day up to each row:

SELECT sale_date, daily_revenue,
       SUM(daily_revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;

3-day moving average, the current day plus the 2 before it:

SELECT sale_date, daily_revenue,
       ROUND(AVG(daily_revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS avg_3d
FROM daily_sales;

ROWS BETWEEN sets the frame:

• 2 PRECEDING: two rows back
• CURRENT ROW: this row
• UNBOUNDED PRECEDING: all the way back to the first row

A moving average smooths out noisy daily numbers so the trend shows. The 7-day version is a classic interview question.