LAG and LEAD in SQL: Lag and Lead Functions Made Simple

LAG and LEAD let you reach to the previous row or the subsequent row in the same sql window so you can calculate period-over-period deltas, churn/retention flags, and sessions without self-joins. Use them when you need to compare the current row to a prior or future event. This guide shows production-ready patterns, pitfalls, and performance tips—skip re-learning OVER/PARTITION BY; for a refresher on window function basics, see SQL Window Functions Explained With Examples and the SQL topic hub.

What are LAG vs LEAD?

LAG looks backward; LEAD looks forward. Both are window function peers and accept an offset and an optional default.

What is the opposite of lag in SQL? LEAD. Together people say lag and lead or lead and lag. When someone asks for a value that must immediately precede another, reach for LAG.

Syntax you actually use for lead and lag functions

Core syntax:

-- Minimal
LAG(value_expr) OVER (PARTITION BY key ORDER BY ts)
LEAD(value_expr, 2, 0) OVER (PARTITION BY key ORDER BY ts)

The partition groups rows (e.g., by customer_id), and ORDER BY defines the sequence within each group. The offset is how many rows to step; default fills null when you run off the window. For OVER/PARTITION details and frame options, defer to the window function guide. We will focus on patterns to analyze time-series data fast.

Use case 1: Period-over-period made simple

Scenario: your orders table has 40M rows, and you need monthly revenue change per country. LAG replaces a self-join on month, and it scales.

WITH orders_by_month AS (
  SELECT
    country,
    DATE_TRUNC(order_ts, MONTH) AS month,
    SUM(amount) AS revenue
  FROM analytics.orders
  GROUP BY country, DATE_TRUNC(order_ts, MONTH)
)
SELECT
  country,
  month,
  revenue,
  LAG(revenue) OVER (PARTITION BY country ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue, 1, 0) OVER (PARTITION BY country ORDER BY month) AS mom_abs_change,
  SAFE_DIVIDE(revenue - LAG(revenue) OVER (PARTITION BY country ORDER BY month),
              NULLIF(LAG(revenue) OVER (PARTITION BY country ORDER BY month), 0)) AS mom_pct_change
FROM orders_by_month
ORDER BY country, month;

Notes:

• Using a default of 0 can be helpful, but consider whether you want null instead for the first month’s delta. Both are fine if you document them.
• Always include a deterministic ORDER BY clause. Add a tie-breaker (e.g., day + surrogate key) if needed.
• To return only the latest month per country, consider QUALIFY to filter by window results without subqueries. See SQL QUALIFY Clause: Filter Window Functions Without Subqueries. For aggregate filtering more generally, compare HAVING vs WHERE in SQL.

Year-over-year

Same idea, just change the grain and offset:

SELECT
  country,
  year,
  revenue,
  LAG(revenue, 1) OVER (PARTITION BY country ORDER BY year) AS prior_year,
  revenue - LAG(revenue, 1) OVER (PARTITION BY country ORDER BY year) AS yoy_change
FROM yearly_revenue;

This pattern generalizes to any time-series: adjust the grain and offset. It’s the simplest way to calculate changes across ordered data without a complex query.

Use case 2: Churn and retention flags

Calculate whether a customer returns next period (LEAD) or has lapsed since their previous order (LAG). This is a classic sql interview favorite because it tests sequencing and partition choices.

WITH customer_orders AS (
  SELECT customer_id, DATE(order_ts) AS order_date, amount
  FROM analytics.orders
)
SELECT
  customer_id,
  order_date,
  amount,
  LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
  CASE WHEN LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
            <= order_date + INTERVAL 90 DAY THEN 0 ELSE 1 END AS churn_90d,
  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date,
  DATE_DIFF(order_date,
            LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY) AS gap_days
FROM customer_orders
ORDER BY customer_id, order_date;

Interpretation:

• LEAD flags churn if no subsequent purchase within 90 days.
• LAG provides the previous row to compute gaps and repeat cadence.
• Use the optional default to avoid null if you need a hard 0/1 at the last row, but most teams keep nulls to signal “no data to compare.”

First/last purchase helpers

SELECT
  customer_id,
  order_date,
  CASE WHEN LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) IS NULL THEN 1 ELSE 0 END AS is_first_row,
  CASE WHEN LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) IS NULL THEN 1 ELSE 0 END AS is_last_row
FROM customer_orders;

This gives you booleans for the first row and last row within each customer.

Use case 3: Sessionization without self-joins

Event streams are sequential data. A common rule: start a new session when the gap between events exceeds 30 minutes.

WITH events AS (
  SELECT user_id, event_ts, event_name
  FROM analytics.events
)
, gaps AS (
  SELECT
    user_id,
    event_ts,
    event_name,
    TIMESTAMP_DIFF(event_ts,
      LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts), MINUTE) AS mins_since_prev
  FROM events
)
SELECT
  user_id,
  event_ts,
  event_name,
  SUM(CASE WHEN mins_since_prev IS NULL OR mins_since_prev > 30 THEN 1 ELSE 0 END)
    OVER (PARTITION BY user_id ORDER BY event_ts
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM gaps
ORDER BY user_id, event_ts;

Why this scales: it avoids self-joins and uses a single pass over each user’s ordered events. If cost matters, review BigQuery SQL Best Practices for Analysts and BigQuery Cost Optimization for partition pruning and compute reduction techniques.

Finding the page that immediately precedes a widget event

Question: “Shouldn’t it return page_view, since that event immediately precedes l_widget?” Use LAG on event_name within each user timeline:

SELECT
  user_id,
  event_ts,
  event_name,
  LAG(event_name) OVER (PARTITION BY user_id ORDER BY event_ts) AS prior_event
FROM analytics.events
QUALIFY event_name = 'l_widget';

If ties on event_ts exist, add a deterministic key to the ORDER BY clause to lock the sequence.

Replacing self-joins with LAG/LEAD

Before window functions, we compared adjacent periods with self-joins on shifted dates. LAG or LEAD are simpler to read and usually faster because the engine processes one ordered stream per partition.

-- Old (self-joins)
SELECT this.month, this.revenue - prev.revenue AS mom
FROM m AS this
LEFT JOIN m AS prev ON prev.month = DATE_ADD(this.month, INTERVAL -1 MONTH);

-- New (lag)
SELECT month, revenue - LAG(revenue) OVER (ORDER BY month) AS mom FROM m;

Fewer shuffles and joins typically mean lower cost. For end-to-end tuning, see our performance guides for analytics linked above.

Common pitfalls and edge cases

• Missing ORDER BY: Without a stable ORDER BY in your window, the “current row” and the prior comparison are undefined. Always order, often by timestamp then a tie-break id.
• Offsets: An offset of 2 skips one more row back/forward. Off-by-one is the top mistake; write the expected mapping as a small table to verify.
• Partition resets: LAG/LEAD never cross partitions. Make sure your business logic truly resets by the chosen key.
• Null vs default: Think about null vs a numeric default. For audits, nulls highlight boundaries. For dashboards, a 0 default may simplify arithmetic. Pick one and document.
• Frames: Most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or equivalent. When cumulative sums meet LAG/LEAD, explicitly set frames to avoid surprises.
• Calendar holes: If a month is missing, MoM changes may be misleading. Consider generating a dense calendar before joining metrics and analyze gaps intentionally.
• Type safety: Compare like with like; cast timestamps and currencies consistently across rows.

Sanity-check your sequence

Does the prior value truly precede the current row? Spot-check a few partitions with smaller queries and print the sequence with both LAG and LEAD to confirm directionality and validate the result set you expect.

dbt example: model + tests

Put sessionization in a dbt model and test it:

-- models/user_sessions.sql
SELECT
  user_id,
  event_ts,
  event_name,
  SUM(CASE WHEN TIMESTAMP_DIFF(event_ts,
      LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts), MINUTE) > 30
      OR LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
      THEN 1 ELSE 0 END)
    OVER (PARTITION BY user_id ORDER BY event_ts
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM {{ ref('events_clean') }};

# models/user_sessions.yml
version: 2
models:
  - name: user_sessions
    tests:
      - not_null:
          column_name: user_id
      - not_null:
          column_name: session_id

FAQs

What is lag vs lead?

Both are analytic functions that read another row relative to the current row. lag reads a previous row; lead reads a subsequent row.

What is lag in SQL query?

LAG returns a value from a previous row in the same partition and order. It’s used to calculate deltas, prior events, and gaps across time-series data.

What’s the opposite of lag in SQL?

LEAD. Use LEAD when you need a value from a subsequent row.

What are lead and lag in Snowflake and how are they used?

Same syntax as above. You’ll typically write LAG(col, 1, NULL) OVER (PARTITION BY key ORDER BY ts). In Snowflake you can QUALIFY window expressions to filter directly; learn more in QUALIFY.

How do LEAD() and LAG() Window Functions work?

For each row in the result set, the engine positions a window over the partition and fetches the value at the given offset before (LAG) or after (LEAD) the current row. The default value is returned when no such row exists.

Can someone explain to me LAG and LEAD?

They are lead and lag functions used to compare adjacent events within an ordered group, ideal when you need to analyze time-series data.

Shouldn’t it return page_view, since that event immediately precedes l_widget?

Yes—if your ORDER BY is deterministic. Use LAG(event_name) with a stable ordering (timestamp + event_id) so the prior event is unambiguous.

What are LAG() and LEAD() Functions?

They are analytic window functions in most engines, including Snowflake, BigQuery, Postgres, and sql server. They are widely used in analytics workloads; see also our SQL topic hub for fundamentals.

Performance notes for analytics and portability

• Prefer lag or lead over self-joins for adjacent comparisons; they stream once per partition.
• Use clustering/partitioning on your warehouse to process only relevant data. See BigQuery best practices.
• These lag and lead functions are supported across major systems. Check your database docs for minor syntax differences.

Troubleshooting checklist

• Did you specify the correct ORDER BY clause and tie-breakers?
• Is the partition key aligned to your business entity?
• Did you choose the right offset and default?
• Are null and zero distinct in your metric story?
• Do tests cover boundaries at the first row and last row?

Advanced tip: Use both directions to cross-validate

Pair lag and lead in the same query to calculate both gaps and “next” spans. This helps verify logic and detect ordering issues quickly.

SELECT
  user_id,
  event_ts,
  LAG(event_ts)  OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts,
  LEAD(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS next_ts
FROM analytics.events;

Final guidance

Reach for lag or lead when you need to compare adjacent records inside an ordered group. Be explicit about ordering, offset, and default handling; test edge cases at partition boundaries; and prefer these tools over self-joins for clarity and speed. If you work with XML payloads in sql server before windowing, see Working with XML in SQL Server for Analytics. For quick local prototyping of time-series on a laptop, try DuckDB.

Want more practice? Try the free graded exercises at /practice.