What does LEAD do in SQL?
LEAD() returns an expression from a following row in the same window partition, using the sequence declared in the window ORDER BY. It keeps the current row, so it is ideal for next-event dates, time-to-next-action, forward-looking deltas, expiry boundaries, and funnel transitions without a self-join.
SELECT customer_id, event_at, event_type,
LEAD(event_at) OVER (
PARTITION BY customer_id
ORDER BY event_at, event_id
) AS next_event_at
FROM customer_events;The last row in each partition has no successor and returns NULL unless you supply a default. LEAD means next ordered row, not tomorrow, next month, or the next required funnel stage. The ordering and population define what “next” means.
Control the expression, offset, default, partition, and order
The expression is the value retrieved from a future row. The optional offset selects how many ordered rows to look ahead; one is the default. The optional default is returned when that row does not exist. PARTITION BY creates independent sequences, while ORDER BY determines successor relationships. Every argument changes meaning, not just syntax.
An offset of two means the second following row, not two hours or two billing cycles. For a duration requirement, retrieve the next timestamp and subtract the current timestamp. For a next-period requirement, build the expected period spine first. A default such as the current timestamp can hide missing successors and should be used only when it represents an approved rule.
Make the next row deterministic
If multiple records share the complete ordering tuple, SQL does not guarantee which peer becomes the successor. Event timestamps frequently collide because of batch ingestion or limited precision. Add a stable, business-valid tie-breaker such as an immutable event ID, source sequence, or version when exact event order is knowable and important.
LEAD(status) OVER (
PARTITION BY ticket_id
ORDER BY changed_at, status_event_id
)Do not invent an arbitrary ordering simply to produce stable output. If simultaneous events have no truthful sequence, aggregate them to one timestamp, model them as a set, or expose ambiguity. Reproducibility is necessary, but a consistently fabricated order is still wrong.
Restart the forward lookup at the correct entity boundary
Without a partition, the last event for one customer can point to the first event for another. Partition keys should identify one independent timeline: account, device, order, session, contract, patient episode, or workflow instance. Multi-tenant data normally needs tenant identity in the partition, even if object IDs appear globally unique today.
Over-partitioning is another failure mode. Adding status or calendar month can reset the sequence before the transition you wanted to observe. Write the phrase “next event within ___” before writing SQL; the blank usually reveals the correct partition. Then test boundary rows for two adjacent entities.
Use LEAD for durations, future values, and validity intervals
Common patterns include time until the next purchase, price change to the next quote, state after the current status, and the exclusive end of a slowly changing dimension row. Calculate the future value once in a CTE, then derive several outputs from it. This prevents formulas from silently using different partitions or ordering rules.
WITH versions AS (
SELECT product_id, valid_from, price,
LEAD(valid_from) OVER (
PARTITION BY product_id
ORDER BY valid_from, version_id
) AS valid_to
FROM product_price_history
)
SELECT * FROM versions;Define whether valid_to is inclusive or exclusive and how open-ended current records are represented. A NULL end often means “still current,” while a synthetic distant date may simplify joins but introduces a policy that must be documented and tested.
Separate the next observed event from the next expected stage
In a product funnel, LEAD reveals what actually happened after each event. It does not enforce the desired stage sequence. A user may view, abandon, return, and purchase; the next observed event after the first view is abandonment, not purchase. If the question is time to the next purchase, filter or condition the event population carefully without deleting evidence needed for the path.
A robust pattern keeps the full event stream, calculates the next event, and separately derives next qualifying events when required. Label each field precisely: next_event_at, next_purchase_at, and next_expected_stage_at are different claims. Test repeated stages, skipped stages, out-of-order ingestion, and sessions that never convert.
Distinguish the next row from the next period
A sparse monthly series can make January point directly to April. That is correct for “next observation” and wrong for “next month.” When continuity matters, generate the expected dates or stages, cross them with the required entities, left join observations, and then apply LEAD. Missing February remains a visible record instead of disappearing from the sequence.
For real events, the gap itself may be the metric. Keep both current and next timestamps so reviewers can verify the interval. Define timezone, timestamp precision, and whether late events can be inserted historically. Recompute affected partitions when backfilled events change successor relationships.
Keep “no successor” separate from “successor value is NULL”
LEAD can return NULL because no requested future row exists or because that row exists and the selected expression is NULL. If the distinction matters, lead a guaranteed non-null row identifier alongside the nullable value. The identifier proves whether a successor exists and prevents accidental classification of a real null value as an unfinished sequence.
Defaults are presentation policy, not missing-data repair. Returning zero for a missing next price invents a price drop; returning the current date for a missing next event creates zero duration. Preserve the raw state, then derive a display value only after business owners approve its meaning.
Measure sort cost, partition skew, and repeated windows
LEAD generally requires data ordered by partition and sequence keys. Large or wide sorts consume memory and can spill to disk. One account with millions of events may dominate otherwise balanced work. Inspect actual row counts, maximum partition size, sort memory, spill metrics, exchanges, and whether an upstream access path already provides useful ordering.
| Signal | Likely mechanism | Evidence to collect |
|---|---|---|
| Sort spill | Large ordered input | Actual rows, width, spill size |
| Slow outlier | Entity-history skew | Partition-size distribution |
| Repeated sort | Incompatible window specs | Complete OVER clauses and plan |
Several LEAD expressions with identical partitioning and ordering can often share window work. Tiny differences in sort direction, collation, null ordering, or partition keys can prevent reuse. Confirm behavior in the actual engine plan instead of assuming an index or textual cleanup will help.
Validate forward-looking logic with adversarial sequences
- Test a one-row partition and verify the final-row state.
- Test duplicate timestamps with and without a valid tie-breaker.
- Test missing periods, skipped stages, repeated states, and late events.
- Confirm that partitions never cross tenants or entities.
- Preserve current row, next row, keys, and timestamps for reconciliation.
- Distinguish next observed event from next qualifying event.
- Check NULL successor values separately from absent successors.
- Measure maximum partition size, sort memory, spills, and runtime distribution.
Diagnose negative waiting time after late event ingestion
Suppose a support dashboard shows negative time to next status. The query orders by ingestion timestamp, but the business sequence is event timestamp plus source sequence. A delayed “opened” event arrived after “resolved,” so ingestion order points forward to an earlier business time. Keep ingestion and event timestamps together, identify affected tickets, and restate the required chronology.
After changing the ordering, compare old and new successor pairs, verify all durations are explainable, and add a late-arrival fixture to regression tests. Monitor events whose ingestion delay exceeds the accepted window. This turns a suspicious metric into a documented data-timeliness rule rather than masking negative values with an absolute function.
Check the database-specific LEAD contract
Major databases support LEAD, but accepted argument forms, null-handling options, execution operators, date subtraction, and plan diagnostics vary. PostgreSQL documents LEAD with window functions; Microsoft documents T-SQL syntax and null options. Use the documentation for the exact deployed version and record compatibility assumptions in review notes.
Do not treat successful parsing in one dialect as proof that another engine shares the same semantics. Test a minimal fixture containing ties, NULL values, a custom offset, a default, and a final row. Preserve expected output beside the query.
Inspect the complete LEAD query before review
Paste a sanitized complete statement into the InfiniSynapse SQL Complexity Checker. Review windows together with joins, CTEs, filters, nested layers, repeated specifications, and structural risk; then verify chronology and runtime with your database's actual plan.
Open SQL Complexity CheckerRemove credentials, secrets, personal data, and sensitive literals.SQL LEAD frequently asked questions
It returns an expression from a following ordered row in the same partition.
It returns NULL or the optional default because no requested successor exists.
No. It means next row; continuous periods require an expected-date spine.
Peers with incomplete ordering may receive different successor relationships.
Calculate the next timestamp once, subtract the current timestamp, and preserve missing successors.
Official LEAD documentation
Document the successor rule before release
A reviewer should be able to answer five questions without reverse-engineering the SQL: what entity defines a partition, which columns fully order its events, what the requested offset means, how an absent successor is represented, and whether late data can change historical pairs. Record these answers beside the metric definition and include one small input-to-output fixture.
For operational dashboards, also define a recomputation horizon and freshness signal. A technically correct LEAD result can still mislead if the newest partition is incomplete or if future events arrive after the report cutoff. Showing data completeness keeps “no next event yet” distinct from “the sequence is final.”