A monthly sales table stores this month's amount, but a report also needs last month's amount and the change between them. You could join the table to itself. For many ordered comparisons, LAG() and LEAD() make the intention easier to read.
LAG(value) reads a value from an earlier row in the window order. LEAD(value) reads from a later row. The current row remains in the result. These functions are useful for changes in revenue, gaps between events and comparing successive measurements. They work with rows in an ordered result, however, so “previous row” does not always mean “previous calendar month.” That distinction matters as soon as the data contains a missing period.
Define the order before comparing values
A window expression has the form LAG(amount) OVER (ORDER BY month_start). The order inside OVER tells SQL which observation precedes the current one. Without a meaningful order, there is no dependable previous observation. Use a unique tie-breaker when several events can share the same timestamp.
An optional offset specifies how far to look. LAG(amount, 2) looks two rows backward; it does not subtract two months from a date. A third argument supplies a default when the requested row does not exist. That default does not replace an actual NULL stored in an existing preceding row.
PARTITION BY creates independent sequences. A sales report with several stores should usually partition by store ID and order by month. Otherwise the preceding row for one store can belong to another store. The partition must match the entity whose history you are comparing, not just a convenient category in the table.
Calculate absolute and percentage changes
Use a CTE to name the previous value, then calculate the change in an outer query. This avoids repeating the window expression and makes the first observation's missing comparison visible.
WITH history AS (
SELECT month_label, amount,
LAG(amount) OVER (ORDER BY month_label) AS previous_amount
FROM monthly_sales
)
SELECT month_label, amount - previous_amount AS change,
100.0 * (amount - previous_amount)
/ NULLIF(previous_amount, 0) AS percent_change
FROM history
ORDER BY month_label;February's absolute change is 30 and its percentage change is 30 percent. March's change is negative 10. The decimal literal 100.0 helps avoid integer division. NULLIF prevents division by zero and represents an undefined percentage as NULL; whether the dashboard displays a dash or another label belongs to the presentation policy.
Do not round intermediate values unnecessarily. Calculate using an appropriate exact numeric type for financial data, then format the final percentage. A zero baseline does not become a meaningful percentage simply because you replace it with 1.
Keep the previous amount visible while reviewing the report. An absolute change of 40 can mean modest growth from 1,000 or a large change from 20. Showing the baseline lets a reader check the calculation and interpret its scale without guessing.
Missing dates, NULLs and dialect differences
If the requirement genuinely means consecutive calendar months, build or use a calendar table and left join sales onto it. Decide whether a missing month means zero sales or unknown sales before filling values. Only then apply LAG to the complete month sequence. Filling every absence with zero can hide an incomplete import.
Filtering also changes the sequence. A WHERE amount > 100 in the window's input removes January before LAG runs. To compare every month and then show only larger months, calculate the window first and apply the filter outside the CTE. Explain this order when a colleague sees an unexpected previous value.
PostgreSQL, modern SQLite, MySQL 8+ and SQL Server support these functions, but NULL-skipping options and restrictions on offset expressions vary. Do not assume an IGNORE NULLS clause is available everywhere. The PostgreSQL reference explicitly documents its NULL behavior. Keep portable examples to the common positional comparison and verify advanced options in your engine's manual.
Practice: compare store histories
Imagine a second store has January sales of 500 and February sales of 450. Add a store ID column to a fresh version of the practice table and include both stores. Predict what happens if the window orders only by month label: several same-month observations have no dependable internal order, and previous values can cross store boundaries.
The solution is PARTITION BY store_id ORDER BY month_label, together with one row per store and month. Add a composite uniqueness constraint on those two columns if the data model requires that rule. If the table instead stores individual purchases, summarize purchases to monthly totals before comparing months.
For an event sequence, the solution may be different: partition by account and order by event time plus event ID. The important skill is choosing the sequence from the question rather than copying a window clause whose data grain does not fit.
SQL LAG and LEAD Examples
Each example shows the code, the output it produces and what each part does.
Compare four observations
The following practice table uses text month labels that sort correctly because their format is YYYY-MM. Real applications may use an actual date column representing the first day of each month.
January has no preceding observation and May has no following observation. April is absent entirely. May's previous amount therefore comes from March. The output is correct for adjacent observed rows but does not by itself establish a month-over-month comparison.
CREATE TABLE monthly_sales (
month_label VARCHAR(7) PRIMARY KEY,
amount INTEGER NOT NULL
);
INSERT INTO monthly_sales VALUES
('2026-01', 100), ('2026-02', 130),
('2026-03', 120), ('2026-05', 160);
SELECT month_label, amount,
LAG(amount) OVER (ORDER BY month_label) AS previous_amount,
LEAD(amount) OVER (ORDER BY month_label) AS next_amount
FROM monthly_sales
ORDER BY month_label;month_label | amount | previous_amount | next_amount
2026-01 | 100 | NULL | 130
2026-02 | 130 | 100 | 120
2026-03 | 120 | 130 | 160
2026-05 | 160 | 120 | NULLSQL LAG and LEAD FAQ
Is LAG always faster than a self join?
No universal performance promise applies. It often expresses adjacent-row comparisons clearly, but sorting, indexes and data volume determine the plan.
Does LEAD change the stored data?
No. Both functions read values while producing a query result. They do not shift rows in the table.
Should the first previous value be zero?
Only when zero is a justified baseline. Leaving NULL usually communicates that no earlier observation exists. A report can label that case “first observation” rather than inventing history.
Can LAG look back more than one row?
Yes. The second argument is the offset: LAG(amount, 7) OVER (ORDER BY day) reads the value from seven rows earlier, such as the same weekday last week in a daily series. The optional third argument supplies a default instead of NULL when that row doesn't exist.
Does LAG respect PARTITION BY?
Yes. With PARTITION BY store_id, the previous row is looked up only within the same store, and the first row of each store gets NULL rather than a value from another store. Without a partition, comparisons run across the whole ordered result.
SQL LAG and LEAD Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
May follows March in a table where April is missing. What does LAG(amount) ordered by month return for May?
SQL LAG and LEAD Quiz
2 questions to check what you learned.
Which function reads a value from a following row?
- LAG
- LEAD
- COUNT
What does LAG(amount, 1, 0) do when a previous row exists but its amount is NULL?
- It always returns the default 0
- It skips the previous row automatically
- It returns that stored NULL