SQL Academy · Lesson

Running Totals and Moving Averages

Compute running sums and moving averages with SUM(...) OVER (ORDER BY ... ROWS BETWEEN ...).

Running Total Basics

A running total is a SUM with ORDER BY in the window — and no frame clause, so the default frame is "from the start to the current row":

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

Reset the running total per user:

SELECT id, user_id, total,
       SUM(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS user_running_total
FROM orders;