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

LAG reads a value from an earlier row and LEAD reads a value from a later row without collapsing the result. They are useful when the meaning of a row depends on sequence: previous purchases, next events, period-over-period changes, sessions, and retention.

The ordering is part of the business rule. A window should identify the entity being followed, the column that establishes sequence, and a deterministic tie-breaker when two records can share the same timestamp.

The core pattern

SELECT
  customer_id,
  order_date,
  LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
  ) AS previous_order_date,
  LEAD(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
  ) AS next_order_date
FROM orders;

PARTITION BY restarts the sequence for each customer. ORDER BY defines which row is previous or next. The first LAG and final LEAD in each partition return NULL; that boundary needs business interpretation rather than an automatic label.

Checked example: sessionize events in BigQuery

Dialect: GoogleSQL for BigQuery. Run the setup, then the query. The 30-minute rule starts a new session only when the gap is greater than 30 minutes.

Setup data

CREATE TEMP TABLE user_events (
  user_id STRING,
  event_at TIMESTAMP
);

INSERT INTO user_events VALUES
  ('ada', TIMESTAMP '2026-01-03 09:00:00+00'),
  ('ada', TIMESTAMP '2026-01-03 09:20:00+00'),
  ('ada', TIMESTAMP '2026-01-03 10:05:00+00'),
  ('bo',  TIMESTAMP '2026-01-03 09:00:00+00'),
  ('bo',  TIMESTAMP '2026-01-03 09:30:00+00');

Query

WITH previous_events AS (
  SELECT
    user_id,
    event_at,
    LAG(event_at) OVER (
      PARTITION BY user_id
      ORDER BY event_at
    ) AS previous_event_at
  FROM user_events
),
new_session_flags AS (
  SELECT
    *,
    CASE
      WHEN previous_event_at IS NULL
        OR TIMESTAMP_DIFF(event_at, previous_event_at, MINUTE) > 30
      THEN 1
      ELSE 0
    END AS is_new_session
  FROM previous_events
)
SELECT
  user_id,
  event_at,
  SUM(is_new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM new_session_flags
ORDER BY user_id, event_at;

Expected output

Edge case: A gap of exactly 30 minutes remains in the same session because the rule starts a new session only when the gap is greater than 30 minutes.

BigQuery does not allow one window function to refer to another window function in its argument list or OVER clause. That is why the query calculates the previous timestamp, derives the flag, and only then applies the cumulative window.

Period-over-period comparisons

Aggregate to the reporting period before applying LAG. Otherwise the previous row may be another transaction rather than the previous month.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC(order_date, MONTH) AS order_month,
    SUM(net_revenue) AS revenue
  FROM orders
  GROUP BY order_month
),
with_previous AS (
  SELECT
    *,
    LAG(revenue) OVER (ORDER BY order_month) AS previous_revenue
  FROM monthly_revenue
)
SELECT
  order_month,
  revenue,
  previous_revenue,
  SAFE_DIVIDE(revenue - previous_revenue, previous_revenue) AS change_rate
FROM with_previous
ORDER BY order_month;

A missing calendar month is still missing from this result. Join to a date spine first when “previous period” must mean the previous calendar month rather than the previous observed row.

Checked example: classify churn only after enough follow-up

A final observed purchase does not prove churn unless the dataset continues for the full threshold after that purchase. This example makes the observation end explicit.

Dialect: GoogleSQL for BigQuery.

Setup data

CREATE TEMP TABLE purchases (
  customer_id STRING,
  purchase_date DATE
);

INSERT INTO purchases VALUES
  ('ada', DATE '2025-01-01'),
  ('ada', DATE '2025-04-15'),
  ('bo',  DATE '2025-04-15'),
  ('cy',  DATE '2025-01-01');

Query

WITH parameters AS (
  SELECT DATE '2025-05-01' AS observation_end
),
next_purchases AS (
  SELECT
    customer_id,
    purchase_date,
    LEAD(purchase_date) OVER (
      PARTITION BY customer_id
      ORDER BY purchase_date
    ) AS next_purchase_date
  FROM purchases
)
SELECT
  customer_id,
  purchase_date,
  next_purchase_date,
  CASE
    WHEN next_purchase_date IS NOT NULL
      AND DATE_DIFF(next_purchase_date, purchase_date, DAY) > 90
    THEN 'churned'
    WHEN next_purchase_date IS NOT NULL
    THEN 'not_churned'
    WHEN DATE_ADD(purchase_date, INTERVAL 90 DAY) <= observation_end
    THEN 'churned'
    ELSE 'insufficient_follow_up'
  END AS churn_status
FROM next_purchases
CROSS JOIN parameters
ORDER BY customer_id, purchase_date;

Expected output

Edge case: A final purchase is churned only when the observation window extends at least 90 days beyond it; otherwise the outcome is insufficient follow-up.

Common mistakes

• Leaving out a tie-breaker. If timestamps can tie, add a stable event or order identifier to the window ordering.
• Using the previous observed row as the previous calendar period. Build a complete period spine when gaps matter.
• Nesting window calculations. Use a CTE for each logical stage in dialects such as BigQuery.
• Treating a boundary NULL as a business outcome. Decide whether it means first event, no later event, or insufficient observation.

Practice the same reasoning

Continue with the linked LAG/LEAD exercise to compare each purchase with the prior and next purchase. Then use the SaaS subscriptions dataset to apply the same sequence and observation-window reasoning to retention.

Primary references

• Window function calls — Google Cloud
• Navigation functions — Google Cloud