---
title: "LAG and LEAD"
description: "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."
canonical: "https://motherduck.com/glossary/lag-and-lead/"
related:
  - title: "Window functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions/"
  - title: "GoodShip: Building an Analytics Agent for Global Logistics"
    url: "https://motherduck.com/case-studies/goodship-ai-transportation-analyst/"
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
---

# 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.

```sql
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`).

```sql
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:

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