Period-over-Period Analysis in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this LAG and LEAD, Date Truncation and Extraction
What is 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 — 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.
How do you compare this month to last month in SQL?
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 monthWITH monthly 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
FROM monthly 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.
Why does LAG need PARTITION BY for per-entity trends?
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. The tell is at the boundaries: with PARTITION BY, the first period of every entity has a NULL prior value, because there is nothing before it. Without it, only the very first row of the whole result does — every other entity's first period quietly borrows the last value of the entity before it.
How do you write a year-over-year comparison in SQL?
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.
Practice Period-over-Period in SQL
Scenario: Brightlane's finance team is preparing a monthly revenue summary and wants each month's total displayed alongside the prior month's total for easy comparison.
Task: 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.
Assumptions:
- A calendar month is identified by its first day and covers every order placed within that month.
- The earliest month in the data has no preceding month; its
prev_month_revenuevalue is missing.
Output:
- One row per calendar month present in the data.
- Columns in this order:
month(the first day of the calendar month),revenue,prev_month_revenue. - Sorted by
monthascending.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Period-over-Period 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.
Start learning to practice all 10 Period-over-Period problems, with instant grading and mastery tracking.
Common questions about Period-over-Period
How do I calculate month-over-month growth as a percentage in SQL?
Bring last month onto the current row with LAG, then divide the change by it: (this month minus last month) divided by last month, times 100. Wrap the divisor in NULLIF(last_month, 0), because a prior month of zero would otherwise stop the query with a division by zero error.
Why is the first month’s growth NULL?
LAG has no earlier row to read for the first period, so it returns NULL, and any arithmetic involving NULL is NULL. That is the correct answer rather than a bug: there is nothing before the first month to compare it with.
What goes wrong if a month is missing from the data?
LAG reads the previous row, not the previous calendar month. If March has no data, April is compared with February and the growth figure is silently wrong. Fill the gaps with a date spine before applying LAG, so every month has a row.
How do I compare year over year?
Use the same shape with a larger offset. Over monthly rows, LAG(value, 12) reaches back twelve rows to the same month last year. It depends on every month being present, for the same reason as a missing month above.
