Rolling Totals

Imagine we are helping a store manager track the total sales over time. They do not just want to know how much was sold on a particular day; they want to see the running total of sales as each new order comes in. This helps them understand trends, see how revenue is growing, and make timely business decisions. That is exactly where the Rolling totals pattern comes into play. It helps us calculate ongoing sums or averages over an ordered sequence of data.

In this lesson, we’ll learn how to use Rolling totals pattern that calculates cumulative totals and moving averages. We'll explore why these rolling metrics matter, how to use them effectively, and how to apply them in practical scenarios such as tracking monthly sales, building dashboards, or comparing growth over time.

By the end of this lesson, we’ll be able to:

• Understand what rolling totals are and where they’re useful.

• Use SQL window functions like SUM() OVER() and AVG() OVER() to compute cumulative and moving totals.

• Learn to recognize the pattern and apply it effectively.

Pattern overview

Category:

• Aggregation Patterns

Intent:

To calculate cumulative metrics, such as running totals or moving averages, over a series of ordered records.

Motivation:

Understanding growth and change over time is key in data analysis. Rolling totals help us answer questions like:

• How are sales increasing month by month?

• What is the cumulative revenue per customer?

• What’s the 7-day moving average of daily sales?

These metrics reveal performance trends and help detect surges, drops, or patterns that single-point metrics might miss.

Also known as:

• Running Totals

• Moving Window Aggregation

• Cumulative Sums

• Moving Averages

Structure

This pattern is implemented using SQL window functions with an OVER() clause. We specify:

• The partition (optional, grouping scope, like by customer or product).

• The ordering (such as by date).

• The window frame (for moving averages).

Typical structure for cumulative total: