LAG and LEAD on a Data Engineer interview

Why interviewers love LAG and LEAD

LAG and LEAD are the two window functions that let you reach into the previous or next row without writing a self-join. On a Data Engineer interview at Stripe, Snowflake, or Databricks, the loop is almost always the same: a panelist hands you a stream of events, an orders table, or a daily metrics table and asks for week-over-week growth, gap detection, or session boundaries. If you reflexively reach for a self-join, the next 20 minutes will be painful — and the interviewer will already be writing "needs more SQL reps" on the rubric.

The cost of not knowing this pattern is concrete. A junior DE typically writes a 30-line self-join, the plan shows two sequential scans plus a hash join, and the query runs for ten minutes on a 200M-row table. The LAG version is three lines, one sort, and finishes in under a minute on the same hardware. That delta — the same answer in 10x less wall time — is exactly what the senior loop screens for.

Basic syntax

LAG(expression [, OFFSET [, default]]) OVER (
    [PARTITION BY ...]
    ORDER BY ...
)

• expression — the value to fetch from a neighbor row.
• offset — how many rows back (LAG) or forward (LEAD). Defaults to 1.
• default — what to return when the neighbor is missing. Defaults to NULL.
• PARTITION BY — independent windows (e.g., per user_id).
• ORDER BY — mandatory, defines what "neighbor" means.

A minimal worked example — the previous order amount for the same user:

SELECT
  order_id,
  user_id,
  order_at,
  amount,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY order_at) AS prev_amount,
  LEAD(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_at) AS next_amount
FROM orders;

LEAD(amount, 1, 0) returns the next amount and falls back to 0 when no next row exists. That third argument is the cleanest way to avoid COALESCE wrappers downstream.

Compare with the past: revenue growth

The single most common LAG question is month-over-month growth. The template:

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS growth_pct
FROM monthly
ORDER BY month;

NULLIF(..., 0) is the guard against division by zero — the first month always has a NULL previous value, and any month with zero revenue would otherwise blow up. The senior-grade version factors LAG into its own CTE so it appears once, not three times:

WITH monthly AS (...),
shifted AS (
  SELECT
    month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly
)
SELECT month, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 2) AS growth_pct
FROM shifted;

This second form is what an interviewer means when they say "rewrite for readability".

Gap detection in time series

A daily-orders table with missing days is the classic LAG question. Find the days where orders did happen, and measure the gap to the previous active day:

SELECT
  d.day,
  LAG(d.day) OVER (ORDER BY d.day) AS prev_day,
  d.day - LAG(d.day) OVER (ORDER BY d.day) AS gap_days
FROM (
  SELECT DISTINCT DATE(created_at) AS day FROM orders
) d
ORDER BY day;

A gap_days > 1 row marks a silent stretch. If gap_days = 5, four consecutive days had zero activity. The alternative is a left join against generate_series:

SELECT d.day
FROM generate_series('2026-01-01'::DATE, '2026-12-31'::DATE, '1 day') AS d(day)
LEFT JOIN (SELECT DISTINCT DATE(created_at) AS day FROM orders) o USING (day)
WHERE o.day IS NULL;

The two are not interchangeable. generate_series lists every missing day individually — useful when you want to fill them. LAG measures the length of each gap — useful when you want to flag stretches longer than some SLO threshold.

Sessionization: 30-minute gap rule

A textbook DE interview question: split a stream of events into sessions, where a new session starts when the gap exceeds 30 minutes.

WITH events_with_gap AS (
  SELECT
    user_id,
    event_at,
    event_type,
    EXTRACT(EPOCH FROM (event_at - LAG(event_at) OVER (
      PARTITION BY user_id ORDER BY event_at
    ))) AS gap_seconds
  FROM events
),
sessions AS (
  SELECT
    *,
    SUM(CASE WHEN gap_seconds IS NULL OR gap_seconds > 1800 THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
  FROM events_with_gap
)
SELECT user_id, session_id, MIN(event_at), MAX(event_at), COUNT(*) AS events
FROM sessions
GROUP BY 1, 2;

The logic in three steps:

1. For each event, compute the gap to the previous event of the same user.
2. Mark a row as a session start if the gap is NULL (first event) or greater than 1800 seconds.
3. A running sum of those 1s produces a stable session_id per user.

This gap-based sessionization pattern shows up at almost every senior DE loop — Snowflake, Databricks, Stripe, Notion, Linear all use variants of it for their product analytics. Expect a follow-up like "what if the threshold should be per-user adaptive?" The answer is usually: precompute per-user median gap, materialize it, and join.

Distributed engines: ClickHouse and Spark

The standard ANSI window syntax works on Postgres, BigQuery, Snowflake, and Spark SQL with identical semantics. ClickHouse is the outlier — and that's a favorite trap for staff DE candidates who claim "I do ClickHouse daily".

ClickHouse supports lagInFrame and leadInFrame on recent versions. The legacy form is neighbor(), now deprecated.

SELECT
  user_id,
  event_at,
  lagInFrame(amount) OVER (
    PARTITION BY user_id ORDER BY event_at
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
  ) AS prev_amount
FROM events;

Spark SQL mirrors Postgres syntactically:

SELECT user_id, amount,
       lag(amount) OVER (PARTITION BY user_id ORDER BY ts) AS prev
FROM events;

The Spark gotcha is skewed partitions. If one user has 100M events and others have 1k, the window for that one user runs on a single executor — the rest of the cluster waits. Mitigations: a salted partition key for the hot user, or a preprocessing pass that splits the heavy partition.

BigQuery and Snowflake both let you use IGNORE NULLS directly inside the window — a small but real productivity win when your event stream has sparse columns.

Common pitfalls

The most expensive mistake is omitting ORDER BY. LAG and LEAD are defined relative to row order — without it, the engine is free to return rows in any sequence, and "previous" loses its meaning. Postgres will error in some cases and silently return garbage in others. The fix is mechanical: every LAG and every LEAD in your query must have an ORDER BY inside its OVER clause.

A second common trap is dropping PARTITION BY when partitions matter. If the question is "amount of the previous order for the same user", LAG(amount) OVER (ORDER BY order_at) will happily compare two different users' orders. The bug is invisible on a 10-row dev sample and obvious only after the dashboard ships to stakeholders. Always read the prompt for the unit of analysis — per user, per account, per session — and partition by it.

A third pitfall is using LAG inside a WHERE clause. Window functions are evaluated after WHERE, so the parser will reject the construct. The workaround is to wrap the LAG call in a CTE or subquery and filter on the resulting column:

-- broken
SELECT * FROM orders WHERE LAG(amount) OVER (...) > 100;

-- working
WITH t AS (SELECT *, LAG(amount) OVER (...) AS prev FROM orders)
SELECT * FROM t WHERE prev > 100;

A fourth issue is dividing by a null previous value. The first row of each partition has a NULL prev — (curr - prev) / prev will return NULL at best and error on strict engines. Wrap the denominator in NULLIF(prev, 0) if you want the safe fallback, or add a WHERE prev IS NOT NULL filter if rows with no history should be excluded entirely.

A fifth is defaulting to a self-join instead of LAG. Self-joins are five to ten times slower on real data because the planner has to scan the table twice and run a hash or merge join. LAG does one scan and one sort. On a 500M-row events table, that's the difference between a one-minute interactive query and a coffee break.

A sixth is treating LAG as "the row physically above in the table". LAG returns the previous row in the window order, not in the on-disk layout. If you forget to specify ORDER BY or order by a non-unique column, the result is undefined. Always add a tie-breaker — ORDER BY event_at, event_id — when timestamps can repeat.

Finally, trying to add a frame clause like ROWS BETWEEN ... to LAG or LEAD is a smell. These functions ignore frame clauses; they use the offset argument instead. Postgres will accept the syntax silently — the frame has no effect — but ClickHouse and some other engines require it on lagInFrame. Know which dialect you're in.

Related reading

• SQL window functions interview questions
• Window functions for Data Engineer interview
• Recursive CTE for Data Engineer interview
• SQL for Data Engineer interview prep
• EXPLAIN and query plan for Data Engineers
• How to do gap analysis in SQL

If you want a daily drill loop with hundreds of LAG, LEAD, and sessionization problems graded for interview prep, NAILDD is launching with exactly that bank.

FAQ

What is the difference between LAG and FIRST_VALUE?

LAG returns the value N rows back from the current row in window order, defaulting to one row back. FIRST_VALUE returns the first row in the window frame — and the frame matters. By default, the frame in most engines is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so FIRST_VALUE returns the partition's starting row. If you specifically want "the very first value in this partition", use FIRST_VALUE with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Mixing them up is a classic mid-level interview mistake.

Can I pass an expression as the offset argument?

In Postgres and Snowflake, the offset must be a constant or a query parameter — not a per-row expression. BigQuery is slightly more permissive but still expects a constant at planning time. If you genuinely need a dynamic offset, the workarounds are a CASE over fixed offsets, a self-join with a computed key, or in extreme cases a procedural loop. In nearly every interview scenario, a constant offset is correct and "I'd need a procedural step" is the right answer.

Does LAG work on NULL values?

Yes. LAG returns NULL when the source value is NULL, and the standard SQL:2008 modifiers IGNORE NULLS and RESPECT NULLS let you control behavior. Oracle and Snowflake support both fully; Postgres added IGNORE NULLS in version 16; BigQuery and Spark have supported it for years. Mention this in interviews — it signals you've actually shipped sparse event-stream queries, not just toy datasets.

How do I handle duplicates in the ORDER BY?

When two rows tie on the ORDER BY column, the order between them is undefined and your LAG result becomes non-deterministic. The fix is mandatory in production code: add a tie-breaker that is guaranteed unique, such as ORDER BY event_at, event_id. Without a tie-breaker, you can get different answers on different runs of the same query — and that's a guaranteed flag at a code review at Stripe or Databricks.

Is LAG always faster than a self-join?

On any non-trivial table, yes — typically 5 to 10 times faster. A self-join needs two scans of the source plus a hash or merge join; LAG does one scan plus one sort. The exception is tiny tables already indexed in exactly the right order, where a nested loop may end up similar. In every other case, reach for LAG.

What if I need both LAG and LEAD in the same query?

Stack them in the same SELECT. As long as the OVER clauses are identical, the engine sorts the partition once and computes both on the same pass — one of the reasons window functions are so cheap compared to self-joins.