SQL LEAD and LAG Window Functions: Period-over-Period Calculations

By Sharon Ben-Moshe · October 2, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Master SQL LEAD() and LAG() window functions. Calculate month-over-month growth, compare sequential events, and supply default values without self-joins.

Quick answer

LEAD() and LAG() are SQL window functions used to access data from subsequent (LEAD) or previous (LAG) rows relative to the current row without writing expensive self-joins. Both functions accept three parameters: LEAD(expression, [offset], [default_value]), allowing period-over-period growth and time-delta calculations.

FAQs

What is the difference between LEAD and LAG?

LAG looks backwards in the sorted partition to return values from preceding rows. LEAD looks forwards to return values from succeeding rows.

What is the default offset if not specified?

If omitted, the offset defaults to 1 (the immediately previous or next row). You can pass higher integers like LAG(revenue, 12) for year-over-year monthly comparisons.

How do I prevent NULL on the first or last row?

Provide the third argument as a fallback default value: `LAG(amount, 1, 0)` returns 0 instead of NULL when no previous row exists.

Can LEAD and LAG be used without an ORDER BY clause?

No. Positional window functions require an unambiguous row sequence defined by ORDER BY inside the OVER() clause.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

LAG(col, 1) inspects the preceding row; LEAD(col, 1) inspects the following row.

Specify a third parameter as a default fallback value to avoid NULL on boundary rows.

Partition by entity ID (e.g., PARTITION BY customer_id) to prevent boundary values from bleeding across entities.

Widely supported in PostgreSQL, MySQL 8+, SQL Server, SQLite 3.25+, and Google BigQuery.

Calculating month-over-month sales growth

Here is the canonical pattern for comparing monthly revenue to the prior month's revenue to compute percentage change:

Month-over-month revenue growth using LAG

SELECT
  sales_month,
  revenue,
  LAG(revenue, 1, 0) OVER (
    ORDER BY sales_month ASC
  ) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1, revenue) OVER (ORDER BY sales_month ASC))
    * 100.0 / NULLIF(LAG(revenue, 1, 0) OVER (ORDER BY sales_month ASC), 0),
    2
  ) AS mom_growth_pct
FROM monthly_sales;

Detecting session inactivity and time gaps

In web analytics and IoT telemetry, finding user session idle times or equipment downtime is a core use case:

Finding time elapsed between user events

SELECT
  user_id,
  event_time,
  event_name,
  LAG(event_time) OVER (
    PARTITION BY user_id
    ORDER BY event_time ASC
  ) AS prev_event_time,
  -- Time elapsed since last user action in seconds
  EXTRACT(EPOCH FROM (
    event_time - LAG(event_time) OVER (
      PARTITION BY user_id
      ORDER BY event_time ASC
    )
  )) AS seconds_since_last_event
FROM user_audit_logs;

Common mistakes to avoid

Forgetting PARTITION BY: without partitioning by customer/store, the first month of Store B will compare itself against the last month of Store A.

Assuming chronological row order in the table: always specify explicit ORDER BY in OVER().

Forgetting to wrap divisors with NULLIF when calculating growth ratios, risking division by zero.

SQL LEAD and LAG Window Functions: Period-over-Period Calculations

Quick answer

FAQs

What is the difference between LEAD and LAG?

What is the default offset if not specified?

How do I prevent NULL on the first or last row?

Can LEAD and LAG be used without an ORDER BY clause?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Calculating month-over-month sales growth

Detecting session inactivity and time gaps

Common mistakes to avoid

Official references

Quick answer

FAQs

What is the difference between LEAD and LAG?

What is the default offset if not specified?

How do I prevent NULL on the first or last row?

Can LEAD and LAG be used without an ORDER BY clause?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Calculating month-over-month sales growth

Detecting session inactivity and time gaps

Common mistakes to avoid

Related SQL guides

Official references