Baldwin Apps

Posted on Jun 27

SQL Pattern Series #9: The Period-over-Period Pattern

#sql #database #beginners #tutorial

Comparing each row to the one that came before it

SQL Pattern Series #9 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:

How to compare a row to the previous row
When to use LAG()
How period-over-period analysis works
Why this pattern is useful for trends and reporting

Many reports answer a simple question:

What happened this month?

But decision-makers often want a different question answered:

How did this month compare to last month?

That is where the Period-over-Period Pattern comes in.

The Problem

Suppose you have monthly revenue data:

Month      Revenue
---------- -------
Jan-2025    10000
Feb-2025    12000
Mar-2025    11000
Apr-2025    15000

Looking at the values individually is useful.

But it doesn't immediately tell you:

Which months increased?
Which months decreased?
By how much?
What trends are emerging?

To answer those questions, each row needs access to the previous row.

The Period-over-Period Pattern

The Period-over-Period Pattern uses window functions to compare each row against a prior period.

Most commonly:

LAG(column) OVER (
    ORDER BY column
)

LAG() retrieves a value from a previous row without requiring a self-join.

Conceptually, SQL is asking:

What was the value in the previous period?

Example Using LAG

SELECT
    Month,
    Revenue,
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS PreviousRevenue
FROM MonthlyRevenue;

Result:

Month      Revenue   PreviousRevenue
---------- --------- ----------------
Jan-2025    10000    NULL
Feb-2025    12000    10000
Mar-2025    11000    12000
Apr-2025    15000    11000

Each row now has access to the previous period's value.

Calculating the Difference

Once the previous value is available, calculating the change becomes simple.

SELECT
    Month,
    Revenue,
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS PreviousRevenue,
    Revenue -
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS RevenueChange
FROM MonthlyRevenue;

Result:

Month      Revenue   PreviousRevenue   RevenueChange
---------- --------- ---------------- -------------
Jan-2025    10000    NULL              NULL
Feb-2025    12000    10000             2000
Mar-2025    11000    12000            -1000
Apr-2025    15000    11000             4000

Now increases and decreases become obvious.

Why This Pattern Matters

Many business questions are really comparison questions:

Revenue growth
Subscriber growth
Daily active users
Download counts
Inventory changes
Website traffic

Looking at raw numbers alone often hides the trend.

Comparing each period to the previous one reveals movement.

A Note on Ordering

The ordering column matters.

For example:

LAG(Revenue) OVER (
    ORDER BY Month
)

The previous value depends entirely on the specified order.

If the ordering is incorrect, the comparison will also be incorrect.

Always verify that the ordering column reflects the actual sequence you intend to analyze.

Beyond One Period

LAG() can also look farther back.

For example:

LAG(Revenue, 3) OVER (
    ORDER BY Month
)

This retrieves the value from three rows earlier.

Useful for:

Quarter-over-quarter comparisons
Seasonal analysis
Historical benchmarking

When I Reach for This Pattern

I typically use the Period-over-Period Pattern when:

comparing revenue trends
analyzing subscriber growth
tracking downloads
measuring engagement changes
identifying sudden increases or decreases

Examples include:

month-over-month revenue
week-over-week traffic
day-over-day activity
year-over-year comparisons

Key Takeaway

Many reports become more useful when you stop asking:

What happened?

and start asking:

What changed?

The Period-over-Period Pattern helps answer that question by giving each row access to the one that came before it.

Sometimes the trend is more important than the value itself.