LAG and LEAD

Why it matters

"Revenue is 51,800 this month" means little on its own. "Revenue is up 17.5% on last month" is a headline. Period-over-period change (day over day, month over month, year over year) is in nearly every business report, and so are related questions: how many days between a customer's orders, or did a sensor reading jump compared with the previous one? LAG and LEAD answer all of these by letting a row look at its neighbours.

The core idea

• LAG(col) returns col from the previous row in the window order.
• LEAD(col) returns col from the next row.

SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_revenue,
       LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM monthly_revenue
ORDER BY month;

The first row has no previous row, so LAG returns NULL; the last row has no next row.

Both functions take two optional extra arguments: LAG(col, offset, default). LAG(revenue, 12) looks 12 rows back (year over year on monthly data), and LAG(revenue, 1, 0) returns 0 instead of NULL when there is no earlier row. Be careful with the default: a 0 makes the first month look like infinite growth.

Month-over-month change

Compute the previous value in a CTE, then do the arithmetic. That keeps each expression short:

WITH with_prev AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT month,
       revenue,
       revenue - prev_revenue AS change,
       ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS pct_change
FROM with_prev
ORDER BY month;

The percentage formula is (new - old) / old × 100. Two details matter:

• 100.0, not 100. In SQLite and PostgreSQL, dividing two integers gives an integer: 3500 * 100 / 42000 is 8, not 8.33.
• Division by zero. If last month was 0, the result is meaningless. SQLite returns NULL, PostgreSQL raises an error. Guard it with NULLIF(prev_revenue, 0) in the denominator, which turns a 0 into NULL.

Per-entity comparisons with PARTITION BY

Add PARTITION BY so each customer, store or product is compared only with its own history:

-- Days since each customer's previous order
SELECT customer_id, order_date,
       julianday(order_date)
         - julianday(LAG(order_date) OVER (PARTITION BY customer_id
                                           ORDER BY order_date)) AS days_since_prev
FROM orders;

julianday turns a date string into a day number in SQLite (PostgreSQL would subtract dates directly). Without PARTITION BY, the first order of customer 2 would be compared with the last order of customer 1, which is nonsense.

Year over year needs complete periods

LAG(revenue, 12) assumes exactly one row per month with no gaps. If a month is missing, "12 rows back" is 13 months back. For reliable comparisons:

1. Aggregate to one row per period in a CTE (for example GROUP BY substr(order_date, 1, 7)).
2. Make sure every period exists (left-join onto a calendar if needed).
3. Then apply LAG.

FIRST_VALUE and LAST_VALUE

Related functions compare with a fixed point instead of a neighbour. FIRST_VALUE(revenue) OVER (ORDER BY month) returns January's revenue on every row, so you can show "growth since the start of the year". LAST_VALUE needs an explicit frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), because the default frame stops at the current row; this catches many people out.

Common mistakes

• Mixing up LAG and LEAD. LAG looks back (previous), LEAD looks forward (next).
• Missing PARTITION BY, so values leak between customers or stores.
• Integer division and dividing by zero in percentage change.
• Running LAG on raw transactions instead of on one row per period.
• Filtering before the window. WHERE month >= '2026-03' in the same query removes February, so March's prev_revenue becomes NULL. Filter in an outer query after computing LAG.

Quick recap

• LAG = previous row, LEAD = next row, ordered by the window's ORDER BY.
• Percent change = (new - old) * 100.0 / NULLIF(old, 0).
• Use PARTITION BY for per-entity history and aggregate to one row per period first.
• Filter after the window function, not before.

Try it

Open the Do tab and build a month-over-month revenue report with LAG.