Learn how to compare two time periods — month-over-month, quarter-over-quarter — in a single SQL query using derived tables, FULL OUTER JOIN, and CASE-based percentage change logic. Includes CTEs, conditional aggregation alternatives, and a hands-on exercise with realistic data.
Period-over-period analysis is one of the most common requests a data professional will receive. "How did we do this month compared to last month?" "Is this quarter better than the same quarter last year?" "What's the week-over-week change in active users?" These questions land in your inbox constantly, and the analyst who can answer them quickly — with a clean, correct SQL query — is the one people trust.
The deceptively tricky part isn't the math. Calculating percentage change is arithmetic you learned in middle school. The hard part is structuring the SQL so that you're computing two separate aggregates — one for this period, one for the prior period — and then joining them together cleanly in a single result set. Get this wrong and you either get two separate query results you manually compare in a spreadsheet, or worse, a single query that silently double-counts or misaligns your periods.
By the end of this lesson, you'll have a reliable, repeatable pattern for period-over-period queries. You'll understand how to filter and aggregate each period independently, how to bring those results together using derived tables and JOINs, and how to compute percentage change in the same query. We'll work through a realistic e-commerce scenario from a raw transactions table all the way to a production-ready report.
What you'll learn:
WHERE clauseGROUP BY and derived tablesSELECTThis lesson assumes you're comfortable with SELECT, WHERE, GROUP BY, and basic JOIN syntax. If you need a refresher on aggregation functions, Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG is a good warm-up. You should also understand how derived tables (subqueries in the FROM clause) work at a conceptual level — if that's unfamiliar, Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views will fill in that background.
Throughout this lesson, we'll use a simple but realistic orders table from an e-commerce platform:
-- orders table structure
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
product_category VARCHAR(50),
revenue DECIMAL(10, 2),
status VARCHAR(20) -- 'completed', 'refunded', 'pending'
);
A few sample rows to anchor your mental model:
| order_id | customer_id | order_date | product_category | revenue | status |
|---|---|---|---|---|---|
| 1001 | 42 | 2024-02-14 | Electronics | 249.99 | completed |
| 1002 | 87 | 2024-02-15 | Apparel | 59.95 | completed |
| 1003 | 42 | 2024-03-01 | Electronics | 189.00 | refunded |
| 1004 | 103 | 2024-03-08 | Home & Garden | 74.50 | completed |
We want to compare revenue by product category: February 2024 vs. January 2024. This is a realistic monthly business review ask.
Before you can compare periods, you need to reliably select rows that belong to each one. This sounds obvious, but date filtering is where a lot of analysts make quiet, hard-to-catch errors.
The most portable approach is explicit date range bounds using >= and <:
-- January 2024
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
-- February 2024
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
Notice the pattern: the upper bound is always < the first day of the next period, not <= the last day of this period. This avoids ambiguity with timestamps. If your order_date column is a TIMESTAMP or DATETIME type rather than a DATE, then <= '2024-01-31' will miss any order placed after midnight on January 31st — a real bug that shows up in production.
Warning
Never use BETWEEN with date columns unless you are absolutely certain they contain no time component. BETWEEN '2024-01-01' AND '2024-01-31' excludes '2024-01-31 14:30:00' because that timestamp is technically greater than '2024-01-31'. Use >= start AND < next_period_start instead — always.
You can also use date truncation functions if your database supports them. In PostgreSQL, DATE_TRUNC('month', order_date) = '2024-01-01' works cleanly. In MySQL, YEAR(order_date) = 2024 AND MONTH(order_date) = 1 is readable but can't use a standard index on order_date. The range bound approach (>= / <) is the most index-friendly and most portable across databases. For a deeper look at how SQL handles date functions across dialects, see Master SQL String and Date Functions: Essential Data Transformation Skills.
Now that you know how to filter each period, the next step is to aggregate each one into a summary. Each period becomes its own derived table — a subquery that lives inside the FROM clause and behaves like a regular table.
Here's the "current period" derived table (February 2024):
SELECT
product_category,
SUM(revenue) AS current_revenue,
COUNT(order_id) AS current_orders
FROM orders
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
GROUP BY product_category
And here's the "prior period" derived table (January 2024):
SELECT
product_category,
SUM(revenue) AS prior_revenue,
COUNT(order_id) AS prior_orders
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
GROUP BY product_category
Each query produces one row per product_category with that category's totals for its respective month. They're structurally identical except for the date range and the column aliases.
Tip
Always give your aggregated columns distinct, descriptive aliases (current_revenue vs prior_revenue). Once you JOIN these two derived tables together, both will be in scope simultaneously. Ambiguous names like just revenue will either cause errors or silent confusion.
Run each of these independently first. Verify the row counts and totals look right before you try to join them. This is a key habit when building complex queries — confirm each layer before stacking the next one.
Here's where the query comes together. We wrap each aggregation in parentheses to form a derived table, give each one an alias, and then JOIN them on product_category:
SELECT
COALESCE(curr.product_category, prior.product_category) AS product_category,
COALESCE(curr.current_revenue, 0) AS current_revenue,
COALESCE(prior.prior_revenue, 0) AS prior_revenue,
COALESCE(curr.current_orders, 0) AS current_orders,
COALESCE(prior.prior_orders, 0) AS prior_orders
FROM (
-- Current period: February 2024
SELECT
product_category,
SUM(revenue) AS current_revenue,
COUNT(order_id) AS current_orders
FROM orders
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
GROUP BY product_category
) AS curr
FULL OUTER JOIN (
-- Prior period: January 2024
SELECT
product_category,
SUM(revenue) AS prior_revenue,
COUNT(order_id) AS prior_orders
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
GROUP BY product_category
) AS prior
ON curr.product_category = prior.product_category
ORDER BY current_revenue DESC;
Notice we're using a FULL OUTER JOIN rather than an INNER JOIN. This is intentional and important. If a product category had orders in January but zero orders in February, an INNER JOIN would silently drop it from the results entirely — making it look like that category simply doesn't exist rather than showing a decline to zero. The FULL OUTER JOIN preserves it, and COALESCE(..., 0) converts the NULL values from the missing side into zeros.
Key insight
Choosing FULL OUTER JOIN vs LEFT JOIN vs INNER JOIN here is a business logic decision, not just a SQL syntax choice. Ask yourself: what do I want to happen when a category exists in one period but not the other? The answer determines the join type. See Choosing the Right JOIN Type: When to Use INNER, LEFT, and FULL OUTER JOIN for Clean Aggregation Results for a thorough breakdown of when each join type is appropriate.
If you're on MySQL (which doesn't support FULL OUTER JOIN natively), you can simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN, or restructure the query using conditional aggregation, which we'll cover shortly.
Now that both periods are side by side in the same result set, calculating the change is straightforward arithmetic in the outer SELECT. Let's extend the query:
SELECT
COALESCE(curr.product_category, prior.product_category) AS product_category,
COALESCE(curr.current_revenue, 0) AS current_revenue,
COALESCE(prior.prior_revenue, 0) AS prior_revenue,
COALESCE(curr.current_revenue, 0)
- COALESCE(prior.prior_revenue, 0) AS revenue_change,
CASE
WHEN COALESCE(prior.prior_revenue, 0) = 0 THEN NULL
ELSE ROUND(
(COALESCE(curr.current_revenue, 0) - COALESCE(prior.prior_revenue, 0))
/ prior.prior_revenue * 100,
1
)
END AS revenue_pct_change
FROM (
SELECT
product_category,
SUM(revenue) AS current_revenue,
COUNT(order_id) AS current_orders
FROM orders
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
GROUP BY product_category
) AS curr
FULL OUTER JOIN (
SELECT
product_category,
SUM(revenue) AS prior_revenue,
COUNT(order_id) AS prior_orders
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
GROUP BY product_category
) AS prior
ON curr.product_category = prior.product_category
ORDER BY revenue_pct_change DESC NULLS LAST;
The percentage change formula is (current - prior) / prior * 100. The CASE expression guards against division by zero when the prior period revenue was zero — in that case we return NULL rather than an error or a meaningless infinity. If you'd prefer to label those rows explicitly, you can return a string like 'New Category' in the CASE result, though that requires a consistent data type approach.
Note
In the ORDER BY, we use NULLS LAST (supported in PostgreSQL and Oracle) to push categories with no prior period to the bottom of the results. In MySQL, you can achieve the same with ORDER BY revenue_pct_change IS NULL, revenue_pct_change DESC.
For deeper reading on COALESCE and NULL handling in computed columns, NULL Handling in SQL: IS NULL, COALESCE, and NULLIF covers the full toolkit.
The derived table version above works perfectly, but once you add more logic — filtering, additional metrics, or more periods — it becomes dense. Common Table Expressions (CTEs) let you name each derived table and reference it like a named view, making the query easier to read, debug, and modify.
Here's the same query rewritten with CTEs:
WITH current_period AS (
SELECT
product_category,
SUM(revenue) AS current_revenue,
COUNT(order_id) AS current_orders
FROM orders
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
GROUP BY product_category
),
prior_period AS (
SELECT
product_category,
SUM(revenue) AS prior_revenue,
COUNT(order_id) AS prior_orders
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
GROUP BY product_category
)
SELECT
COALESCE(c.product_category, p.product_category) AS product_category,
COALESCE(c.current_revenue, 0) AS current_revenue,
COALESCE(p.prior_revenue, 0) AS prior_revenue,
COALESCE(c.current_revenue, 0)
- COALESCE(p.prior_revenue, 0) AS revenue_change,
CASE
WHEN COALESCE(p.prior_revenue, 0) = 0 THEN NULL
ELSE ROUND(
(COALESCE(c.current_revenue, 0) - COALESCE(p.prior_revenue, 0))
/ p.prior_revenue * 100,
1
)
END AS revenue_pct_change,
COALESCE(c.current_orders, 0) AS current_orders,
COALESCE(p.prior_orders, 0) AS prior_orders
FROM current_period c
FULL OUTER JOIN prior_period p
ON c.product_category = p.product_category
ORDER BY revenue_pct_change DESC NULLS LAST;
This reads almost like a documented spec. You can scan to current_period and immediately understand what it contains, then look at prior_period, and finally read the outer SELECT as pure comparison logic. When a colleague needs to change the date range or add a filter, they know exactly where to make the edit.
Tip
When debugging a CTE-based query, run each CTE block independently by temporarily replacing the final SELECT with SELECT * FROM current_period (or whatever your CTE is named). Most databases allow this when you run the entire WITH ... SELECT block. It's the cleanest way to verify each layer in isolation.
For more on structuring complex queries with CTEs, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture goes deep on when CTEs are preferable to derived tables and how to chain them.
There's another pattern for period-over-period that's worth understanding: conditional aggregation. Instead of aggregating each period separately and joining them, you aggregate the entire date range in one pass and use CASE WHEN inside SUM() to route each row to the right period's total.
SELECT
product_category,
SUM(CASE
WHEN order_date >= '2024-02-01' AND order_date < '2024-03-01'
THEN revenue ELSE 0
END) AS current_revenue,
SUM(CASE
WHEN order_date >= '2024-01-01' AND order_date < '2024-02-01'
THEN revenue ELSE 0
END) AS prior_revenue,
COUNT(CASE
WHEN order_date >= '2024-02-01' AND order_date < '2024-03-01'
THEN order_id ELSE NULL
END) AS current_orders,
COUNT(CASE
WHEN order_date >= '2024-01-01' AND order_date < '2024-02-01'
THEN order_id ELSE NULL
END) AS prior_orders
FROM orders
WHERE status = 'completed'
AND order_date >= '2024-01-01'
AND order_date < '2024-03-01'
GROUP BY product_category
ORDER BY current_revenue DESC;
This is elegant and often faster, because the database scans the table once instead of twice. The trade-off is that it only works cleanly when you can bound both periods in a single WHERE clause — which is straightforward for adjacent months like January and February, but gets complicated for things like "this week vs. same week last year."
Key insight
For adjacent periods (month-over-month, quarter-over-quarter), conditional aggregation in a single pass is often the most efficient approach. For non-adjacent periods (this month vs. same month last year, or rolling 30-day windows), the derived table / CTE JOIN pattern is usually cleaner and more correct, because the date ranges don't overlap and are harder to bound in a single WHERE clause.
Notice the difference in how SUM and COUNT handle the ELSE clause: SUM uses ELSE 0 (so non-matching rows contribute zero to the total), while COUNT uses ELSE NULL (so non-matching rows are ignored — COUNT counts non-NULL values). This is a subtle but important distinction. COUNT(CASE WHEN ... THEN order_id ELSE 0 END) would count every row, not just the matching ones, because zero is not NULL.
You can wrap the percentage change calculation around this the same way as before — add it to the outer query or nest this as a subquery. Pivoting Query Results in SQL: Using CASE WHEN and GROUP BY to Turn Rows into Columns covers this CASE WHEN inside aggregate pattern in even more depth.
Let's bring everything together in one realistic, production-ready query. The stakeholder request: "Give me a monthly revenue report by product category for February vs. January 2024, showing the absolute change and percentage change, and only include categories where we had at least $500 in current-month revenue."
WITH current_period AS (
SELECT
product_category,
SUM(revenue) AS current_revenue,
COUNT(order_id) AS current_orders,
COUNT(DISTINCT customer_id) AS current_customers
FROM orders
WHERE order_date >= '2024-02-01'
AND order_date < '2024-03-01'
AND status = 'completed'
GROUP BY product_category
),
prior_period AS (
SELECT
product_category,
SUM(revenue) AS prior_revenue,
COUNT(order_id) AS prior_orders,
COUNT(DISTINCT customer_id) AS prior_customers
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01'
AND status = 'completed'
GROUP BY product_category
),
comparison AS (
SELECT
COALESCE(c.product_category, p.product_category) AS product_category,
COALESCE(c.current_revenue, 0) AS current_revenue,
COALESCE(p.prior_revenue, 0) AS prior_revenue,
COALESCE(c.current_revenue, 0)
- COALESCE(p.prior_revenue, 0) AS revenue_change,
CASE
WHEN COALESCE(p.prior_revenue, 0) = 0 THEN NULL
ELSE ROUND(
(COALESCE(c.current_revenue, 0) - COALESCE(p.prior_revenue, 0))
/ p.prior_revenue * 100,
1
)
END AS revenue_pct_change,
COALESCE(c.current_orders, 0) AS current_orders,
COALESCE(p.prior_orders, 0) AS prior_orders,
COALESCE(c.current_customers, 0) AS current_customers,
COALESCE(p.prior_customers, 0) AS prior_customers,
COALESCE(c.current_customers, 0)
- COALESCE(p.prior_customers, 0) AS customer_change
FROM current_period c
FULL OUTER JOIN prior_period p
ON c.product_category = p.product_category
)
SELECT
product_category,
current_revenue,
prior_revenue,
revenue_change,
revenue_pct_change,
current_orders,
prior_orders,
current_customers,
prior_customers,
customer_change
FROM comparison
WHERE current_revenue >= 500
ORDER BY revenue_pct_change DESC NULLS LAST;
The three-CTE structure — current_period, prior_period, comparison — mirrors the way a thoughtful analyst would reason through the problem. Each CTE has a single job. The final SELECT applies the business filter (WHERE current_revenue >= 500) and sorting. Anyone maintaining this query six months from now will understand exactly what's happening and where to make changes.
This structure also scales well. Want to add average order value? Add SUM(revenue) / COUNT(order_id) AS avg_order_value inside each period CTE and carry it through. Want to compare three periods? Add a same_period_last_year CTE and join it in. The pattern is modular.
Use the following dataset (you can create it in any SQL database) and write the queries described below.
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
region VARCHAR(30),
product_line VARCHAR(30),
amount DECIMAL(10,2),
status VARCHAR(20)
);
INSERT INTO sales VALUES
(1, '2024-03-05', 'Northeast', 'Software', 1200.00, 'completed'),
(2, '2024-03-12', 'Northeast', 'Hardware', 850.00, 'completed'),
(3, '2024-03-19', 'Southeast', 'Software', 430.00, 'completed'),
(4, '2024-03-22', 'Southeast', 'Services', 990.00, 'completed'),
(5, '2024-03-28', 'West', 'Hardware', 670.00, 'refunded'),
(6, '2024-04-02', 'Northeast', 'Software', 1540.00, 'completed'),
(7, '2024-04-09', 'Northeast', 'Hardware', 720.00, 'completed'),
(8, '2024-04-11', 'Southeast', 'Software', 680.00, 'completed'),
(9, '2024-04-15', 'West', 'Services', 1100.00, 'completed'),
(10, '2024-04-20', 'West', 'Hardware', 930.00, 'completed'),
(11, '2024-04-25', 'Southeast', 'Services', 310.00, 'completed'),
(12, '2024-04-29', 'Northeast', 'Services', 475.00, 'completed');
Exercise tasks:
Write a query that compares total completed sales by region between March 2024 and April 2024, showing the absolute dollar change for each region.
Extend that query to include percentage change. Handle the case where a region had zero sales in March (use NULL rather than dividing by zero).
Use conditional aggregation (the SUM(CASE WHEN ...) approach) to produce the same result in a single table scan.
Add a filter so only regions with at least $1,000 in April sales appear in the final output.
Expected insight: Notice that the West region has no completed sales in March (the only March row for West was refunded). Your FULL OUTER JOIN version should show West with a NULL or zero for prior_revenue. Your conditional aggregation version should naturally handle this too, since matching rows simply contribute zero.
The single most common bug. Using order_date <= '2024-01-31' instead of order_date < '2024-02-01' will miss January 31 transactions if the column stores timestamps. Always use the open upper bound pattern.
Diagnostic: Run SELECT MIN(order_date), MAX(order_date) FROM orders WHERE [your filter] to verify your bounds are capturing what you expect.
If a product category appeared in January but had zero completed orders in February, an INNER JOIN between the two derived tables drops it entirely. The result looks complete but silently omits the biggest story — a category that went dark.
Diagnostic: Run each derived table independently and count the distinct product_category values. Then count distinct categories in the joined result. If the number drops, you have a join type problem.
In the conditional aggregation pattern, writing COUNT(CASE WHEN condition THEN order_id ELSE 0 END) counts every row, including non-matching ones, because 0 is not NULL. Use ELSE NULL with COUNT, or use SUM(CASE WHEN condition THEN 1 ELSE 0 END) if you want conditional counting.
If you filter by status = 'completed' in one CTE but forget it in the other, your periods become incomparable. Always apply the same business logic filters — status, channel, region exclusions — identically in both periods.
In the percentage change formula, you need the raw prior_revenue from the CTE (not the COALESCEd version) as the denominator — and guard it with the CASE WHEN ... = 0 THEN NULL check. If you use COALESCE(p.prior_revenue, 0) as the denominator, the CASE check on = 0 handles the zero case, but make sure the logic flows correctly:
-- Correct: guard comes first, then use raw column in denominator
CASE
WHEN COALESCE(p.prior_revenue, 0) = 0 THEN NULL
ELSE ROUND(
(COALESCE(c.current_revenue, 0) - p.prior_revenue)
/ p.prior_revenue * 100,
1
)
END
-- Also correct but slightly redundant: use COALESCE in denominator
-- since CASE already guarantees it's non-zero at this point
CASE
WHEN COALESCE(p.prior_revenue, 0) = 0 THEN NULL
ELSE ROUND(
(COALESCE(c.current_revenue, 0) - COALESCE(p.prior_revenue, 0))
/ COALESCE(p.prior_revenue, 0) * 100,
1
)
END
Both work. The key is the guard condition comes first.
Warning
Integer division is a silent killer in some databases. In PostgreSQL, 1 / 4 returns 0 because both operands are integers. In the revenue change formula, if your revenue column is DECIMAL, you're fine. But if you're computing percentage change on integer counts (like order counts), cast explicitly: CAST(current_orders AS DECIMAL) / prior_orders * 100.
If product_category can be NULL in your data, a JOIN on category = category will never match NULL rows from both sides — because NULL = NULL is NULL in SQL, not TRUE. Use COALESCE(c.product_category, 'Uncategorized') before joining, or handle NULLs explicitly in the join condition. This is a surprisingly common issue in real-world data where category assignment is incomplete.
For a systematic approach to debugging queries when results look wrong, Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step walks through the full diagnostic process.
For small to medium datasets (millions of rows or fewer), the CTE join approach is fine. For very large tables, two things help:
1. Indexes on the date and status columns. The query pattern used here filters heavily on order_date and status. A composite index on (order_date, status) or at minimum an index on order_date will make a significant difference. See SQL Indexes Explained: How They Work and When to Create Them for how to reason about index design for query patterns like this.
2. Prefer conditional aggregation over two-CTE joins when periods are adjacent. A single table scan with SUM(CASE WHEN ...) is almost always faster than two separate scans, because the planner can read the table once and split rows into buckets in memory. For non-adjacent periods (this month vs. same month last year), you'll scan 13 months of data instead of 2, which might be worse — measure before assuming.
You now have a complete, production-ready toolkit for period-over-period SQL analysis:
>= start AND < next_period_start with explicit literals for correctness and index compatibility.FULL OUTER JOIN to preserve categories that appear in only one period, and COALESCE to convert NULL sides to zero.CASE WHEN prior = 0 THEN NULL guard before computing the ratio.SUM(CASE WHEN ...) is often cleaner and faster.These patterns generalize far beyond revenue — you can apply them to user counts, conversion rates, support ticket volumes, or any metric where "how does this period compare to the last?" is the question.
Where to go next:
LAG(), explore how analytic functions handle period comparison with even less boilerplate. Many production BI teams use window functions for this pattern once they've mastered the derived table approach.