Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Rolling Totals

Rolling Totals

Learn about the Rolling totals pattern to calculate cumulative sums and moving averages over time.

We'll cover the following...

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:

SUM(Column) OVER (PARTITION BY ... ORDER BY ...)
Syntax to find cumulative total

Typical structure for moving average:

AVG(Column) OVER (
    PARTITION BY
    ORDER BY
    ROWS BETWEEN N PRECEDING AND CURRENT ROW
)
Syntax to find moving average

Keywords

SUM() OVER(), AVG() OVER(), PARTITION BY, ORDER BY, ROWS BETWEEN

Problem structure

We use the Rolling Totals pattern when:

  • We need to compute cumulative or rolling aggregates over time-based or sequential data.

  • We define how the data should be grouped (for example, by customer, product, etc).

  • We specify the order of the data (for example, by date, timestamp, or another sequential field).

  • We compute cumulative or rolling totals (for example, total sales up to that point).

  • Look for keywords like: “running total,” “cumulative,” “up to date,” “total so far,” or “rolling sum” to identify when this pattern applies.

Example use cases

1. Track cumulative sales by day.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

You’re working with the Orders table, which includes an OrderDate and TotalAmount for each transaction.

Write an SQL query to calculate daily sales and the running total of sales for the first 10 days of March 2025. The output should include:

  • OrderDate: The date of the order

  • DailySales: The total amount of sales for that day

  • RunningTotal: A cumulative sum of daily sales up to that date (inclusive), ordered by OrderDate

Only include orders where the OrderDate is between March 1 and March 10, 2025.

Press + to interact
SELECT   OrderDate,
         SUM(TotalAmount) AS DailySales,
         SUM(SUM(TotalAmount)) OVER (ORDER BY OrderDate) AS RunningTotal
FROM     Orders
WHERE    OrderDate BETWEEN '2025-03-01' AND '2025-03-10'
GROUP BY OrderDate
ORDER BY OrderDate;

2. Calculate customer lifetime spend over time.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

You’re analyzing the Orders table, which includes:

  • CustomerID: The customer who placed the order

  • OrderDate: The date the order was placed

  • TotalAmount: The value of the order

Write an SQL query to return a running total of spending for each customer, ordered by the date of their orders. The output should include:

  • CustomerID

  • OrderDate

  • TotalAmount

  • CustomerRunningTotal: The cumulative total of all their orders up to and including that date

The query should calculate the running total separately for each customer using a window function.

Press + to interact
SELECT   CustomerID,
         OrderDate,
         TotalAmount,
         SUM(TotalAmount) OVER (
             PARTITION BY CustomerID ORDER BY OrderDate
         ) AS CustomerRunningTotal
FROM     Orders
ORDER BY CustomerID, OrderDate;

3. 7-Day moving average of daily sales.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

You are analyzing daily sales trends using the Orders table, which includes: ...