Baldwin Apps

Posted on Jun 9

SQL Pattern Series #4: The Moving Sum Pattern

#beginners #tutorial #database #sql

Seeing what is happening over the last N rows

SQL Pattern Series #4 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a moving sum is
How window functions make moving calculations possible
Why moving sums are useful for trend analysis
When to use ROWS BETWEEN

Most reports start with simple totals.

SELECT SUM(SalesAmount)
FROM SalesData;

But eventually a different question appears:

What has happened over the last 30 days?

or:

What is the rolling total over the last 30 transactions?

That is where the Moving Sum Pattern becomes useful.

The Problem

Looking at individual rows often hides the bigger picture.

For example:

Date	Sales
Jan 1	100
Jan 2	120
Jan 3	95
Jan 4	140

Individual values move up and down.

Sometimes what matters is the cumulative behavior over a recent window.

Questions such as:

What were sales during the last 30 days?
How many errors occurred during the last 100 events?
What is the rolling total of customer purchases?

require more context than a single row provides.

The Moving Sum Pattern

A moving sum calculates the total of the current row plus a specified number of previous rows.

For example:

SUM(value) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING
         AND CURRENT ROW
)

Conceptually this creates a window.

As each row is processed, the window moves forward.

Rows enter the window.

Older rows leave the window.

The calculation updates automatically.

Example

SELECT
    date,
    value,
    SUM(value) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING
             AND CURRENT ROW
    ) AS moving_sum
FROM SalesData;

This produces a rolling 30-row total.

Each result includes:

the current row
the previous 29 rows

The window moves forward one row at a time.

Why This Pattern Matters

Moving sums help reveal trends that individual rows often hide.

Instead of asking:

What happened today?

you begin asking:

What has happened recently?

This makes the pattern useful for:

sales reporting
financial analysis
monitoring systems
operational dashboards
customer activity tracking

The result is often smoother and easier to interpret than raw values alone.

Window Functions Change Everything

Many SQL developers first encounter moving sums when learning window functions.

Without window functions, moving calculations often require:

self joins
correlated subqueries
temporary tables

Window functions provide a cleaner solution.

The Moving Sum Pattern is one of the most common examples.

A Note on Rows vs Dates

One important detail:

ROWS BETWEEN 29 PRECEDING

means:

Previous 29 rows

not:

Previous 29 days

Those are different concepts.

If dates contain gaps, weekends, or missing records, row counts and date ranges may not align.

Always verify that the window matches the business requirement.

When I Reach for This Pattern

I typically use the Moving Sum Pattern when:

I need rolling totals
I want to smooth noisy data
I need trend visibility
The analysis depends on recent activity

Examples include:

rolling sales totals
rolling transaction counts
rolling inventory movement
rolling customer activity

Key Takeaway

Individual rows show events.

Moving sums show context.

The Moving Sum Pattern helps answer:

What has happened recently?

instead of:

What happened on this row?

That shift often makes trends easier to see and decisions easier to make.