Moving Averages & Running Totals

Window functions make it easy to calculate running totals and moving averages — common tasks in analytics.

Running Total

A running total sums values from the start up to the current row.

Example: Calculate cumulative scores:

SUM(score) OVER (ORDER BY student_id) AS running_total

Moving Average

A moving average calculates the average over a sliding window of rows.

Example: 3-row moving average:

AVG(score) OVER (ORDER BY student_id 
ROWS BETWEEN 2 
PRECEDING AND CURRENT ROW) AS moving_avg

Example Query for Running Total

SELECT student_id, score,
SUM(score) OVER (ORDER BY student_id) AS running_total
FROM scores;

Try experimenting with different window frames to customize your calculations!

‍