Using SQL to Compare Current and Prior Period Results: Date Filtering, Self-Joins, and Conditional Aggregation in Practice

Introduction

Picture this: your VP of Sales walks over and asks, "How did we do last month compared to the month before?" Simple question. But if you've never built a period-over-period comparison query before, you're suddenly staring at a single flat table of orders, wondering how to split it into two time windows and compare them side by side in a single result set.

Period-over-period analysis is one of the most requested things in business analytics — month-over-month revenue growth, week-over-week active users, quarter-over-quarter churn. The underlying data is almost always stored in a single table with a timestamp column, which means you have to do the work of separating and comparing the periods in SQL. This isn't hard once you understand the three main techniques for it, but each one has tradeoffs that matter in production.

By the end of this lesson, you'll be able to write production-ready SQL that compares any two time periods — current vs. prior month, this quarter vs. last, year-to-date vs. prior year-to-date — using three distinct approaches: date filtering with self-joins, conditional aggregation with CASE WHEN, and subquery-based comparisons. You'll know when each approach is appropriate, what mistakes to watch out for, and how to build a complete, readable query that can go straight into a report or dashboard.

What you'll learn:

• How to filter date ranges precisely using WHERE clauses and date functions
• How self-joins work for period-over-period comparison, and when they fall apart
• How conditional aggregation with CASE WHEN lets you pivot time periods into columns
• How to use subqueries and CTEs to build clean, multi-period comparisons
• How to calculate percentage change and handle edge cases like division by zero and missing periods

Prerequisites

You should be comfortable with SELECT, JOIN, GROUP BY, and aggregate functions before diving in. If any of those feel shaky, spend some time with SQL JOINs Explained with Real-World Examples and Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG first. You should also understand basic date functions — DATE_TRUNC, DATEADD, DATEDIFF, and similar — covered in Master SQL String and Date Functions: Essential Data Transformation Skills.

The Dataset We'll Use

Throughout this lesson, we'll work with a realistic e-commerce orders table. Here's the structure:

CREATE TABLE orders (
    order_id       INT PRIMARY KEY,
    customer_id    INT,
    sales_rep_id   INT,
    region         VARCHAR(50),
    order_date     DATE,
    revenue        DECIMAL(10, 2),
    status         VARCHAR(20)   -- 'completed', 'refunded', 'cancelled'
);

This is the kind of table you find everywhere: one row per order, a date, an amount, some categorical dimensions. Our goal is to produce results like this:

Let's build up to that.

Step 1: Getting Your Date Filtering Right

Before you can compare two periods, you need to be able to isolate each one. This sounds obvious, but imprecise date filtering is one of the most common sources of off-by-one errors in analytical SQL.

Let's say we want to compare May 2024 (current) to April 2024 (prior). The naive approach looks like this:

-- Current period
SELECT
    region,
    SUM(revenue) AS revenue
FROM orders
WHERE order_date >= '2024-05-01'
  AND order_date <= '2024-05-31'
  AND status = 'completed'
GROUP BY region;

This works, but it has a subtle problem: <= '2024-05-31' behaves differently depending on whether your column is DATE or DATETIME/TIMESTAMP. For a DATETIME column, '2024-05-31' is interpreted as '2024-05-31 00:00:00', which means you'd miss every order placed after midnight on May 31st.

The safer pattern uses a half-open interval:

WHERE order_date >= '2024-05-01'
  AND order_date < '2024-06-01'

This captures every timestamp in May regardless of time component, and it plays nicely with indexes.

For dynamic date ranges — where you want "the current calendar month" without hardcoding dates — most databases give you tools like DATE_TRUNC (PostgreSQL, BigQuery, Snowflake) or DATEADD/EOMONTH (SQL Server):

-- PostgreSQL / Snowflake: current month dynamically
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'

-- SQL Server equivalent
WHERE order_date >= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
  AND order_date <  DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)

And for the prior month:

-- PostgreSQL / Snowflake: prior month dynamically
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND order_date <  DATE_TRUNC('month', CURRENT_DATE)

Once you have these filter expressions locked down, you're ready to start combining periods.

Approach 1: The Self-Join Method

A self-join means joining a table to itself — treating the same physical table as two logically separate sources. For period-over-period comparisons, you aggregate each period separately (usually in a subquery or CTE), then join the two result sets on your grouping dimension.

Here's how that looks for our regional revenue comparison:

-- Step 1: Aggregate current period
WITH current_period AS (
    SELECT
        region,
        SUM(revenue) AS revenue
    FROM orders
    WHERE order_date >= '2024-05-01'
      AND order_date <  '2024-06-01'
      AND status = 'completed'
    GROUP BY region
),

-- Step 2: Aggregate prior period
prior_period AS (
    SELECT
        region,
        SUM(revenue) AS revenue
    FROM orders
    WHERE order_date >= '2024-04-01'
      AND order_date <  '2024-05-01'
      AND status = 'completed'
    GROUP BY region
)

-- Step 3: Join and calculate change
SELECT
    COALESCE(c.region, p.region)         AS region,
    COALESCE(c.revenue, 0)               AS current_revenue,
    COALESCE(p.revenue, 0)               AS prior_revenue,
    ROUND(
        (COALESCE(c.revenue, 0) - COALESCE(p.revenue, 0))
        / NULLIF(p.revenue, 0) * 100,
        1
    )                                    AS change_pct
FROM current_period c
FULL OUTER JOIN prior_period p
    ON c.region = p.region
ORDER BY current_revenue DESC;

Let's walk through what's happening here:

The CTEs each scan orders once and aggregate to one row per region. This is clean, readable, and lets you inspect each period independently before combining them.

The FULL OUTER JOIN is critical. If a region had sales in April but zero sales in May (or vice versa), an INNER JOIN would silently drop that region from results. Using FULL OUTER JOIN ensures you see every region that appeared in either period. COALESCE(c.region, p.region) handles the case where one side is NULL.

COALESCE(c.revenue, 0) converts a missing period to zero rather than NULL, so your arithmetic stays meaningful.

NULLIF(p.revenue, 0) in the denominator prevents division by zero when the prior period had no revenue. If prior revenue is 0, NULLIF turns it into NULL, which makes the whole division return NULL rather than throwing an error. We cover this pattern in depth in NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.

The self-join approach using CTEs is highly readable and easy to debug — you can run each CTE independently to verify the intermediate results. Its main downside is that it requires two full scans of the orders table (once per CTE). For very large tables, that's worth knowing.

Approach 2: Conditional Aggregation with CASE WHEN

The second approach is often more efficient because it reads the table only once. The idea is to use CASE WHEN inside your aggregate functions to "route" each row into the correct period's bucket.

Here's the same query rewritten with conditional aggregation:

SELECT
    region,
    SUM(CASE
            WHEN order_date >= '2024-05-01'
             AND order_date <  '2024-06-01'
            THEN revenue
            ELSE 0
        END)                             AS current_revenue,
    SUM(CASE
            WHEN order_date >= '2024-04-01'
             AND order_date <  '2024-05-01'
            THEN revenue
            ELSE 0
        END)                             AS prior_revenue,
    ROUND(
        (SUM(CASE
                 WHEN order_date >= '2024-05-01'
                  AND order_date <  '2024-06-01'
                 THEN revenue ELSE 0
             END)
         - SUM(CASE
                   WHEN order_date >= '2024-04-01'
                    AND order_date <  '2024-05-01'
                   THEN revenue ELSE 0
               END))
        / NULLIF(SUM(CASE
                         WHEN order_date >= '2024-04-01'
                          AND order_date <  '2024-05-01'
                         THEN revenue ELSE 0
                     END), 0) * 100,
        1
    )                                    AS change_pct
FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-04-01'
  AND order_date <  '2024-06-01'
GROUP BY region
ORDER BY current_revenue DESC;

A few things to notice:

The outer WHERE clause filters to the combined two-month window before any aggregation happens. This reduces the number of rows the database has to process before grouping. The CASE WHEN expressions then split those rows into their respective period buckets inside the SUM.

ELSE 0 in each CASE expression means rows from the "wrong" period contribute zero to that period's sum, rather than being excluded. This is the key insight of conditional aggregation: every row participates in every SUM, but it only contributes to the one it belongs to.

This approach is beautifully explained by connecting it to the concept of pivoting — you're literally turning a time-period dimension into columns. See Pivoting Query Results in SQL: Using CASE WHEN and GROUP BY to Turn Rows into Columns for more on this pattern.

The one place conditional aggregation gets ugly is in the change_pct calculation — you end up repeating the CASE expressions inside a NULLIF inside a division, which is hard to read. There are two ways to fix this. One is to wrap the conditional aggregation in a subquery so you can reference the column aliases:

SELECT
    region,
    current_revenue,
    prior_revenue,
    ROUND(
        (current_revenue - prior_revenue)
        / NULLIF(prior_revenue, 0) * 100,
        1
    ) AS change_pct
FROM (
    SELECT
        region,
        SUM(CASE
                WHEN order_date >= '2024-05-01'
                 AND order_date <  '2024-06-01'
                THEN revenue ELSE 0
            END) AS current_revenue,
        SUM(CASE
                WHEN order_date >= '2024-04-01'
                 AND order_date <  '2024-05-01'
                THEN revenue ELSE 0
            END) AS prior_revenue
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-04-01'
      AND order_date <  '2024-06-01'
    GROUP BY region
) AS period_totals
ORDER BY current_revenue DESC;

The other — and usually better — option is to use a CTE. This is exactly the kind of situation CTEs were made for. For a deeper look at structuring multi-step queries this way, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.

WITH period_totals AS (
    SELECT
        region,
        SUM(CASE
                WHEN order_date >= '2024-05-01'
                 AND order_date <  '2024-06-01'
                THEN revenue ELSE 0
            END) AS current_revenue,
        SUM(CASE
                WHEN order_date >= '2024-04-01'
                 AND order_date <  '2024-05-01'
                THEN revenue ELSE 0
            END) AS prior_revenue
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-04-01'
      AND order_date <  '2024-06-01'
    GROUP BY region
)
SELECT
    region,
    current_revenue,
    prior_revenue,
    ROUND(
        (current_revenue - prior_revenue)
        / NULLIF(prior_revenue, 0) * 100,
        1
    ) AS change_pct
FROM period_totals
ORDER BY current_revenue DESC;

Clean, efficient, readable. This is usually the pattern you want for two-period comparisons.

Approach 3: Subquery-Based Lookups

Sometimes you want to compare the current period's aggregated results against a single scalar value (like total company revenue last month), or you need to join period totals back to a dimension table. In these cases, a scalar subquery in the SELECT clause is the most natural tool.

Here's an example: for each sales rep, show their revenue this month and the company's total revenue last month as a comparison benchmark:

SELECT
    s.sales_rep_id,
    s.rep_name,
    SUM(o.revenue)                          AS current_revenue,
    (SELECT SUM(revenue)
     FROM orders
     WHERE order_date >= '2024-04-01'
       AND order_date <  '2024-05-01'
       AND status = 'completed')            AS company_prior_total,
    ROUND(
        SUM(o.revenue) /
        NULLIF(
            (SELECT SUM(revenue)
             FROM orders
             WHERE order_date >= '2024-04-01'
               AND order_date <  '2024-05-01'
               AND status = 'completed'),
            0
        ) * 100,
        1
    )                                       AS pct_of_prior_total
FROM orders o
JOIN sales_reps s ON o.sales_rep_id = s.sales_rep_id
WHERE o.order_date >= '2024-05-01'
  AND o.order_date <  '2024-06-01'
  AND o.status = 'completed'
GROUP BY s.sales_rep_id, s.rep_name
ORDER BY current_revenue DESC;

The scalar subquery executes once and returns a single value that gets "broadcast" across every row in the outer result. This is clean, but notice we're repeating that subquery twice (once for display, once for the division). Again, a CTE solves this:

WITH prior_total AS (
    SELECT SUM(revenue) AS total
    FROM orders
    WHERE order_date >= '2024-04-01'
      AND order_date <  '2024-05-01'
      AND status = 'completed'
)
SELECT
    s.sales_rep_id,
    s.rep_name,
    SUM(o.revenue)                                AS current_revenue,
    pt.total                                      AS company_prior_total,
    ROUND(SUM(o.revenue) / NULLIF(pt.total, 0) * 100, 1) AS pct_of_prior_total
FROM orders o
JOIN sales_reps s     ON o.sales_rep_id = s.sales_rep_id
CROSS JOIN prior_total pt
WHERE o.order_date >= '2024-05-01'
  AND o.order_date <  '2024-06-01'
  AND o.status = 'completed'
GROUP BY s.sales_rep_id, s.rep_name, pt.total
ORDER BY current_revenue DESC;

The CROSS JOIN prior_total pt attaches the single prior-total row to every row in the join before grouping, which lets you reference pt.total cleanly anywhere in the query.

Building a Complete Period-Over-Period Report

Now let's pull all of this together into something you'd actually ship. The requirement: a monthly revenue report by region, showing the current month, prior month, absolute change, and percentage change, ordered by largest absolute change.

We'll use conditional aggregation (single scan) wrapped in a CTE for the percentage calculation:

WITH monthly_summary AS (
    SELECT
        region,

        -- Current month: May 2024
        SUM(CASE
                WHEN order_date >= '2024-05-01'
                 AND order_date <  '2024-06-01'
                THEN revenue
                ELSE 0
            END)                                  AS current_revenue,

        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-05-01'
                 AND order_date <  '2024-06-01'
                THEN order_id
            END)                                  AS current_orders,

        -- Prior month: April 2024
        SUM(CASE
                WHEN order_date >= '2024-04-01'
                 AND order_date <  '2024-05-01'
                THEN revenue
                ELSE 0
            END)                                  AS prior_revenue,

        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-04-01'
                 AND order_date <  '2024-05-01'
                THEN order_id
            END)                                  AS prior_orders

    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-04-01'
      AND order_date <  '2024-06-01'
    GROUP BY region
)

SELECT
    region,
    current_revenue,
    prior_revenue,
    (current_revenue - prior_revenue)             AS revenue_change,
    ROUND(
        (current_revenue - prior_revenue)
        / NULLIF(prior_revenue, 0) * 100,
        1
    )                                             AS change_pct,
    current_orders,
    prior_orders,
    ROUND(
        CASE
            WHEN current_orders > 0
            THEN current_revenue / current_orders
        END,
        2
    )                                             AS current_avg_order_value,
    ROUND(
        CASE
            WHEN prior_orders > 0
            THEN prior_revenue / prior_orders
        END,
        2
    )                                             AS prior_avg_order_value
FROM monthly_summary
ORDER BY ABS(current_revenue - prior_revenue) DESC;

This query does a lot of practical work:

• Counts distinct orders per period (not just revenue)
• Calculates average order value per period
• Orders by the absolute magnitude of change, so the most impactful regions appear first
• Handles missing data safely with NULLIF and CASE WHEN ... > 0 THEN

You could add a HAVING clause here if you wanted to filter to only regions with meaningful volume — say, HAVING current_orders > 10 OR prior_orders > 10. See Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions for the mechanics of that.

Handling Missing Periods Gracefully

One of the trickiest edge cases in period-over-period comparisons is when a group (region, product, sales rep) exists in one period but not the other. Conditional aggregation handles this naturally — a missing group just sums to zero. But if you're using the self-join approach with CTEs, you need to decide between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN deliberately.

For a decline/growth report where a new region started this month, you want FULL OUTER JOIN so the new region appears with prior_revenue = 0. If an old region went dark, you want to see it too.

Comparing Non-Adjacent Periods: YTD vs. Prior YTD

Everything above compares two adjacent calendar months. But the same techniques apply to any two date ranges — including year-to-date comparisons where the "window" grows as the calendar progresses.

Here's a year-to-date vs. prior year-to-date query, where the current YTD runs through today and the prior YTD covers the same calendar window one year earlier:

WITH ytd_comparison AS (
    SELECT
        region,

        SUM(CASE
                WHEN order_date >= DATE_TRUNC('year', CURRENT_DATE)
                 AND order_date <  CURRENT_DATE + INTERVAL '1 day'
                THEN revenue ELSE 0
            END)  AS current_ytd,

        SUM(CASE
                WHEN order_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
                 AND order_date <  CURRENT_DATE - INTERVAL '1 year' + INTERVAL '1 day'
                THEN revenue ELSE 0
            END)  AS prior_ytd

    FROM orders
    WHERE status = 'completed'
      AND order_date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year'
      AND order_date <  CURRENT_DATE + INTERVAL '1 day'
    GROUP BY region
)
SELECT
    region,
    current_ytd,
    prior_ytd,
    ROUND((current_ytd - prior_ytd) / NULLIF(prior_ytd, 0) * 100, 1) AS ytd_change_pct
FROM ytd_comparison
ORDER BY current_ytd DESC;

The key here is that CURRENT_DATE - INTERVAL '1 year' gives you the same day of the same month last year. So on May 31, 2024, the prior YTD window runs January 1, 2023 through May 31, 2023 — an apples-to-apples comparison.

Performance Considerations

When your orders table has millions of rows, these queries can get slow. Here's what to think about:

Index your date column. A WHERE order_date >= ... AND order_date < ... filter benefits enormously from a B-tree index on order_date. If you're also filtering by status, a composite index on (status, order_date) can let the database skip irrelevant rows before even reading dates. See SQL Indexes Explained: How They Work and When to Create Them for the full picture.

Conditional aggregation beats self-joins for scale. A single-scan query with CASE WHEN inside SUM reads the table once. The CTE-based self-join approach reads it twice. At 10 million rows, that difference is meaningful.

Pre-aggregate into summary tables for dashboards. If the same period-over-period query runs on every dashboard load, consider materializing a monthly summary table and querying that instead. The aggregation query becomes trivially fast when it's running against a 12-row monthly summary rather than 10 million order rows.

Avoid functions on indexed columns in WHERE. WHERE YEAR(order_date) = 2024 can't use an index on order_date in most databases. WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' can. This is a common performance killer in date-filtering queries.

Hands-On Exercise

Build a sales rep performance comparison report using the orders table schema from this lesson. Your query should:

1. Compare each sales rep's revenue in March 2024 (current) vs. February 2024 (prior)
2. Include: sales_rep_id, current_revenue, prior_revenue, revenue_change, change_pct
3. Also include the rank of each sales rep by current revenue (you can use a window function RANK() OVER (ORDER BY current_revenue DESC) — but get the aggregation working first)
4. Filter to only completed orders
5. Exclude sales reps with fewer than 5 orders in either period
6. Handle the case where a sales rep had revenue in one period but not the other

Hints:

• Use conditional aggregation for the two period sums
• Wrap in a CTE to compute change_pct cleanly
• Use COUNT(DISTINCT CASE WHEN ... THEN order_id END) to count orders per period
• Apply the 5-order filter in a HAVING clause or a second CTE layer

Try building it yourself before looking at the solution below.

WITH rep_periods AS (
    SELECT
        sales_rep_id,

        SUM(CASE
                WHEN order_date >= '2024-03-01'
                 AND order_date <  '2024-04-01'
                THEN revenue ELSE 0
            END)                                      AS current_revenue,

        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-03-01'
                 AND order_date <  '2024-04-01'
                THEN order_id
            END)                                      AS current_orders,

        SUM(CASE
                WHEN order_date >= '2024-02-01'
                 AND order_date <  '2024-03-01'
                THEN revenue ELSE 0
            END)                                      AS prior_revenue,

        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-02-01'
                 AND order_date <  '2024-03-01'
                THEN order_id
            END)                                      AS prior_orders

    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-02-01'
      AND order_date <  '2024-04-01'
    GROUP BY sales_rep_id
    HAVING
        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-03-01'
                 AND order_date <  '2024-04-01'
                THEN order_id
            END) >= 5
        OR
        COUNT(DISTINCT CASE
                WHEN order_date >= '2024-02-01'
                 AND order_date <  '2024-03-01'
                THEN order_id
            END) >= 5
)

SELECT
    sales_rep_id,
    current_revenue,
    prior_revenue,
    (current_revenue - prior_revenue)                 AS revenue_change,
    ROUND(
        (current_revenue - prior_revenue)
        / NULLIF(prior_revenue, 0) * 100,
        1
    )                                                 AS change_pct,
    RANK() OVER (ORDER BY current_revenue DESC)       AS current_rank
FROM rep_periods
ORDER BY current_revenue DESC;

Common Mistakes & Troubleshooting

The off-by-one date error. Using <= '2024-05-31' instead of < '2024-06-01' seems identical for DATE columns, but breaks for DATETIME. Always use the exclusive upper bound pattern.

Forgetting the status filter. If your table has cancelled or refunded orders, including them in revenue sums will inflate your numbers. Make sure your WHERE clause scopes to the right order statuses, and apply it consistently in both periods.

Double-counting with non-distinct COUNT. COUNT(order_id) counts rows, not unique orders. If your data can have duplicate rows (from a join that fans out, for example), use COUNT(DISTINCT order_id). On the other hand, if you're counting intentionally (like counting line items, not orders), DISTINCT would give the wrong answer. Know which one you need.

Column alias reuse in WHERE and HAVING. In most SQL databases, you can't reference a SELECT alias in a WHERE or HAVING clause — that's why we use CTEs or subqueries. If you write HAVING current_revenue > 10000 inside the same SELECT that defines current_revenue, you'll get an error. Move the alias definition into a CTE first.

Mixing up NULL and zero in conditional aggregation. SUM(CASE WHEN ... THEN revenue END) returns NULL when no rows match (because SUM of no values is NULL). SUM(CASE WHEN ... THEN revenue ELSE 0 END) returns 0. The first can cause your change_pct calculation to return NULL even when it should return -100%. Use ELSE 0 deliberately, and use COALESCE on your final output to handle any remaining NULLs.

Overcounting with FULL OUTER JOIN when both sides have duplicates. If your CTEs return multiple rows per group (maybe because you forgot a GROUP BY column), the FULL OUTER JOIN will produce a cartesian mess. Always verify CTE row counts match your expected number of groups before joining.

Summary & Next Steps

You've now got three solid techniques for period-over-period comparison in SQL:

1. Self-join with CTEs — readable, easy to debug, requires two table scans
2. Conditional aggregation with CASE WHEN — single scan, efficient, can get verbose
3. Scalar subqueries / CTE broadcast — clean for single-value comparisons or benchmark metrics

The practical playbook: use conditional aggregation for most two-period comparisons. Use CTEs in multiple layers to keep the percentage-change math readable. Use FULL OUTER JOIN (not INNER) when you care about groups that appear in one period but not the other. Always filter your combined date range in the outer WHERE clause to limit what the database scans before grouping.

From here, the natural next step is window functions — particularly LAG() — which let you compare each row to the previous period's value without writing explicit date ranges at all. If you have a dense time series (every month present for every group), LAG() is dramatically more concise than conditional aggregation. See Window Functions: RANK, ROW_NUMBER, and LAG to go deeper on that pattern.

You should also look at Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice for more patterns that use CASE WHEN inside aggregates — the conditional aggregation technique here is part of a broader family of patterns that show up constantly in analytical SQL.