Period-over-Period Analysis in SQL
Period-over-period analysis compares a metric in one time period to the same metric in a prior period of equal length: this week versus last week, this month versus last month, this quarter versus the same quarter last year. The SQL implementation uses LAG to bring a prior period's value into the same row as the current period's value, enabling the comparison as inline arithmetic.
Before this LAG and LEAD, Date Truncation and Extraction
Period-over-period analysis compares a metric in one time period to the same metric in a prior period — this month vs last month, this quarter vs the same quarter last year. It's one of the most requested report types in business analytics. The SQL pattern uses LAG to bring the prior period's value into the current row, where you can compute the difference or percentage change as inline arithmetic.
The query has two layers. An inner CTE aggregates the fact data by period, producing one row per period with the metric value. An outer layer applies LAG to attach each period's prior-period value as an adjacent column. Once both values are in the same row, the math is just subtraction and division.
WITH monthly_revenue AS (
SELECT
date_trunc('month', ordered_at)::date AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
2
) AS pct_change
FROM monthly_revenue
ORDER BY monthThe CTE aggregates revenue by month. LAG in the outer query retrieves the previous month's revenue for each row. The difference and percentage change are computed over the current and prior values in the same row. NULLIF(prior, 0) prevents division-by-zero when a prior month had zero revenue.
The one thing that trips people up
When the analysis needs period-over-period comparison per entity — monthly revenue per product, weekly signups per region — you must include PARTITION BY:
LAG(revenue) OVER (PARTITION BY product_id ORDER BY month)Without PARTITION BY in a multi-entity context, LAG reaches across entity boundaries. The first month for Product B pulls the last month of Product A as its prior value. The result is numerically plausible but analytically wrong, and the error is invisible in the output.
Year-over-year
For year-over-year, use LAG with an offset equal to the number of rows per year. If the data has one row per month, LAG(revenue, 12) looks back 12 rows — which is the same month last year.
The offset is a row count, not a time unit. If you have weekly rows, LAG(revenue, 12) looks back 12 weeks, not 12 months. Match the offset to the periodicity of your aggregated data.
The first rows in each partition have no prior value in the lookback window, so LAG returns NULL. In a year-over-year comparison, the first 12 months produce NULL for the prior-year column. Whether those rows appear in the final output or get filtered out depends on what the report needs to show.
10 Period-over-Period Analysis practice problems
Write a query to return each calendar month, the total `orders` revenue for that month, and the total `orders` revenue for the immediately preceding calendar month.
Write a query to return each calendar month, the number of `orders` placed in that month, and the number of `orders` placed in the immediately following calendar month.
Write a query to return each calendar month, the total number of `events` recorded in that month, and the total number of `events` recorded in the immediately preceding calendar month.
Write a query to return each calendar month, the total `orders` revenue for that month, and the difference between that month's revenue and the immediately preceding month's revenue.
Write a query to return each customer's `customer_id`, calendar month, total spend in that month, and total spend in the immediately preceding month within that same customer's own order history.
Write a query to return each calendar week, the number of `orders` placed in that week, and the number of `orders` placed in the immediately preceding week.
Write a query to return each user's `user_id`, calendar month, total event count in that month, and event count in the immediately preceding month within that same user's own activity history.
Write a query to return each `event_type`, calendar month, number of `events` of that type in that month, and number of `events` of the same type in the immediately preceding month.
Write a query to return each calendar month, the number of `orders` placed in that month, and the number of `orders` placed in the calendar month two months earlier.
Write a query to return each customer's `customer_id`, calendar month, total spend in that month, and total spend in the immediately preceding month within that same customer's own order history — with zero substituted in place of any boundary-case missing value.
These problems are part of the Period-over-Period Analysis lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.