Lesson 21.1

Period over Period in SQL

Bucket the rows by month, then LAG puts last month beside this one. Change and percent change are one subtraction and one division away.

"Are we growing?" is a question about two periods at once: this month and the one before. Lesson 19.2 · LAG and LEAD used lag() to compare one customer's orders. The same function compares months once the rows have been grouped into months.

Pattern

with monthly as (
  select date_trunc('month', order_date)::date as month,
         count(*) as orders
  from orders
  group by month
)
select month,
       orders,
       lag(orders) over (order by month) as prev_orders,
       orders - lag(orders) over (order by month) as change
from monthly
order by month

Two steps: bucket, then compare

date_trunc('month', order_date) rounds every date down to the first of its month, so a GROUP BY on it gives one row per month. It returns a timestamp, and the ::date cast turns it back into a date. The window then runs over the grouped rows, so lag(orders) over (order by month) is the previous month's count. Doing the grouping in a CTE keeps the two steps apart.

Percent change, and dividing by zero

Percent change is the change divided by the earlier value. When the earlier value is 0, the division has no answer. DuckDB returns inf (infinity) without an error, and PostgreSQL stops with one. Wrapping the divisor in nullif(divisor, 0) returns NULL in every engine:

A month that grew from zero

select 100.0 * (12 - 0) / nullif(0, 0) as pct_change

Percent change from zero covers what to report instead.

Check the last period is whole

Before you trust the last row, look at where the data stops:

Where the data ends

select max(order_date) as last_order_date
from orders

Garden Shop's last order is on June 14, so June is half a month. In the exercise below its revenue still beats May's, which makes it a stronger month than the percentage says. Had June come out lower than May, that would not have meant a slowdown. Compare a partial month with the same days of the month before, or leave it out.

Schema · Garden Shop Tables · orders, order_items 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Syntax reference

Need syntax details? See the SQL window function reference

Your task

The starter computes revenue per month, leaving out cancelled orders. Add prev_revenue (the previous month's revenue), change (revenue minus prev_revenue), and pct_change (change as a percentage of prev_revenue, rounded to one decimal). Keep month and revenue first, and sort by month.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: month, revenue, prev_revenue, change, pct_change.
Rows: one per month, January to June 2024.
January's prev_revenue, change, and pct_change are NULL. April is the only month that fell: down 15.5, or -8.3%.

← Back to all lessons

Next · 21.2 Date spines →

Reference

SQL Window Functions

OVER, PARTITION BY, ranking, offsets, and running totals.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Mistake

Why Percent Change From Zero Breaks

A zero prior period gives Infinity or NaN; use NULLIF.

Reference