LAG, LEAD, FIRST_VALUE and LAST_VALUE

Looking at the neighbour row

Many questions compare a row with the one before it: how much did sales change since yesterday, how many days passed since a customer's last order, did the stock price go up or down. Without window functions you would need a self-join on "the previous date", which is awkward. LAG and LEAD reach into the neighbouring row directly.

• LAG(expr) returns expr from the previous row in the window order. On the first row there is no previous row, so it returns NULL.
• LEAD(expr) returns expr from the next row. On the last row it returns NULL.
• Both take two optional arguments: LAG(expr, offset, default). LAG(revenue, 7) looks 7 rows back; LAG(revenue, 1, 0) returns 0 instead of NULL on the first row.

Day-over-day change and growth

SELECT sale_date, revenue,
       LAG(revenue) OVER (ORDER BY sale_date)           AS prev_day,
       revenue - LAG(revenue) OVER (ORDER BY sale_date) AS change
FROM daily_sales
WHERE store = 'Pune'
ORDER BY sale_date;
-- 2025-03-01 | 12000 | NULL  | NULL
-- 2025-03-02 | 15000 | 12000 | 3000
-- 2025-03-03 | 9000  | 15000 | -6000
-- 2025-03-04 | 18000 | 9000  | 9000
-- 2025-03-05 | 14000 | 18000 | -4000
-- 2025-03-06 | 21000 | 14000 | 7000

Growth in percent is the change divided by the previous value, times 100. If the previous value could be 0, wrap it in NULLIF(prev, 0) so you get NULL instead of a division error.

ROUND(100.0 * (revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0), 1) AS growth_pct
-- 2025-03-02: 25    (12000 → 15000)
-- 2025-03-03: -40   (15000 → 9000)
-- 2025-03-04: 100   (9000 → 18000)

Per customer: days between orders

Add PARTITION BY customer_id and LAG only looks back within the same customer. With SQLite's julianday(), which turns a date into a day number, you can subtract dates.

SELECT customer_id, id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       julianday(order_date)
         - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS gap_days
FROM orders
ORDER BY customer_id, order_date;
-- 1 | 101 | 2025-01-05 | NULL       | NULL
-- 1 | 103 | 2025-02-14 | 2025-01-05 | 40.0
-- 1 | 108 | 2025-04-02 | 2025-02-14 | 47.0
-- 2 | 102 | 2025-01-09 | NULL       | NULL
-- 2 | 106 | 2025-03-15 | 2025-01-09 | 65.0
-- ...

FIRST_VALUE and LAST_VALUE

FIRST_VALUE(expr) returns expr from the first row of the frame; LAST_VALUE(expr) from the last row of the frame. FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) puts each customer's first order date on every one of their orders, handy for "days since the customer joined us".

The LAST_VALUE trap

Remember the default frame from topic 3: from the start of the partition up to the current row. So the "last row of the frame" is the current row itself, and LAST_VALUE just returns the current value. To get the true last value of the partition, widen the frame to the end.

SELECT sale_date, revenue,
       LAST_VALUE(revenue) OVER (ORDER BY sale_date) AS wrong_last,
       LAST_VALUE(revenue) OVER (ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS real_last
FROM daily_sales WHERE store = 'Pune' ORDER BY sale_date;
-- 2025-03-01 | 12000 | 12000 | 21000
-- 2025-03-02 | 15000 | 15000 | 21000
-- ...
-- 2025-03-06 | 21000 | 21000 | 21000

The WINDOW clause

When several functions share the same OVER, name it once with a WINDOW clause after WHERE / GROUP BY / HAVING and before ORDER BY, then write OVER w. It works in SQLite, PostgreSQL, MySQL 8 and SQL Server 2022.

SELECT sale_date, revenue,
       LAG(revenue)  OVER w AS prev_day,
       LEAD(revenue) OVER w AS next_day
FROM daily_sales
WHERE store = 'Pune'
WINDOW w AS (ORDER BY sale_date)
ORDER BY sale_date;

Common mistakes

• Forgetting PARTITION BY, so LAG takes the previous row of a different customer or store.
• Forgetting that the first row gives NULL. Use the third argument (a default) or handle NULL in the report.
• Using LAST_VALUE with the default frame and getting the current row. Widen the frame or use FIRST_VALUE with DESC.
• Dividing by a previous value of 0. Use NULLIF(previous, 0).
• Assuming LAG(x, 1) means "yesterday" when days can be missing. It means the previous row.