LAG and LEAD

LAG() and LEAD() are window functions that return a value from a previous or following row within the same result set, commonly used for period-over-period comparisons.

Overview

LAG() and LEAD() let you access another row's value without a self-join. LAG(expr, offset, default) looks backward offset rows (default 1); LEAD(expr, offset, default) looks forward. Both operate within the ordering defined by OVER (... ORDER BY ...), and optionally within a PARTITION BY group.

Copy code
SELECT
  order_date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day_revenue,
  LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue
FROM daily_sales;

Common uses

Period-over-period change: subtract LAG(revenue) from revenue to compute day-over-day or month-over-month deltas.
Gap and session detection: compare a timestamp to LAG(timestamp) to flag gaps larger than a threshold.
Filling defaults: supply a third argument as a default value when there is no prior/following row (instead of NULL).

Copy code
SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date, 1, order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS days_since_last_order
FROM orders;

DuckDB specifics

DuckDB implements LAG()/LEAD() with the standard three-argument signature (expr, offset, default) and also supports the optional IGNORE NULLS / RESPECT NULLS modifiers for skipping null values when looking backward or forward. Combined with DuckDB's INTERVAL type, LAG/LEAD make time-series gap analysis concise:

Copy code
SELECT event_time,
  event_time - LAG(event_time) OVER (ORDER BY event_time) > INTERVAL '30 minutes' AS new_session
FROM events;

Related terms

OFFSET →

The OFFSET clause skips a specified number of rows in a query's result set before returning the remaining rows, commonly used for pagination alongside LIMIT.

DEFAULT value →

A DEFAULT value is a value a database column automatically takes on when an INSERT statement doesn't explicitly specify a value for it.

window functions →

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…

LIMIT →

The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.

NULL →

NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

IN operator →

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

FAQS

It returns NULL by default. You can pass a third argument, LAG(expr, offset, default), to substitute a specific default value instead of NULL.

Yes. PARTITION BY is optional; without it, LAG/LEAD operate over the entire result set ordered by the ORDER BY expression in the OVER clause.