What does LAG do in SQL?
LAG() returns an expression from a previous row in the same window partition according to the declared ordering. The default offset is one. It preserves every current row, so you can calculate changes, elapsed time, state transitions, and prior observations without joining a table to itself.
SELECT account_id, month_start, revenue,
LAG(revenue) OVER (
PARTITION BY account_id
ORDER BY month_start
) AS previous_revenue
FROM monthly_revenue;The first row for each account has no predecessor and returns NULL unless a default is supplied. LAG means previous row, not previous month. If March is absent, April looks back to February. Decide whether that is correct before treating the difference as month-over-month change.
Understand expression, offset, default, partition, and order
The expression is the value retrieved from the prior row. The optional offset chooses how many ordered rows to look back, and the optional default replaces the result when that row does not exist. PARTITION BY sets independent sequences; ORDER BY defines predecessor relationships. These decisions are semantic, not decorative.
An offset of two means the second previous row, not two days or two months. If the business requires a time duration, compare timestamps after LAG or join to a calendar. Defaults should represent a real policy. Substituting zero for an absent predecessor makes the first observation look like growth from zero and can distort averages.
Make the previous row deterministic
If several rows share the complete window ordering tuple, SQL does not promise which peer comes first. A timestamp may not be unique because events arrive in batches or timestamp precision is limited. Add a business-valid secondary key—such as event sequence, version, or immutable event ID—when the exact predecessor matters.
LAG(status) OVER (
PARTITION BY case_id
ORDER BY changed_at, status_event_id
)Do not add an arbitrary key merely to silence nondeterminism. If two events are genuinely simultaneous and order is unknowable, the correct model may treat them as a set, aggregate them, or flag ambiguous lineage. Deterministic output and truthful sequence are related but not identical goals.
Restart LAG at the correct entity boundary
Without a partition, the first row of one customer may look back to the last row of another. Partition keys should identify the independent timeline: device, account, order, session, contract, or another entity. Multi-tenant systems usually require tenant identity as part of the boundary even when entity IDs appear globally unique today.
Over-partitioning is also harmful. Adding state or month to the partition resets the sequence whenever that value changes, potentially preventing the transition you wanted to observe. Start with the enduring entity identity, then justify every additional partition component in words.
Use LAG for deltas, rates, durations, and transitions
A change is current value minus previous value. A percentage change divides that difference by the previous value, with explicit handling for NULL and zero. Duration is current timestamp minus prior timestamp. A transition compares current and previous states. Compute the prior expression once in a CTE or subquery rather than repeating the same LAG call in several formulas.
WITH compared AS (
SELECT m.*,
LAG(value) OVER (
PARTITION BY metric_id ORDER BY measured_at
) AS prior_value
FROM measurements m
)
SELECT *, value - prior_value AS delta
FROM compared;Repeated textual window expressions may be optimized together, but one named result reduces review risk and guarantees every downstream formula uses the same predecessor. Keep raw current value, prior value, and delta available for reconciliation.
Distinguish previous row from previous period
A sparse series needs a calendar or expected-period spine when the question is period-over-period. Build the required dates for each entity, left join observations, then choose whether missing values remain NULL, carry forward, interpolate, or become zero. Each method encodes a different assumption and should be documented outside the SQL as well.
For event sequences, the gap may be the signal. LAG can reveal elapsed time between purchases, outages, or status changes without creating synthetic rows. Label the result “time since previous event,” not “daily change.” Validate expected maximum gaps and late-arriving events that can insert themselves into historical order.
Keep no predecessor separate from a NULL predecessor
Two situations can produce NULL: the requested previous row does not exist, or it exists but its expression is NULL. A default argument usually applies to the first case, not necessarily to null expression values. If the distinction matters, also lag a guaranteed non-null row key and use it as evidence that a predecessor exists.
Some engines offer options to ignore null values, but support and details differ. That changes “previous row” into “previous non-null observation,” which can cross long gaps. Test it explicitly and include engine-specific documentation in review notes.
Review sort cost, skew, and reusable window specifications
LAG normally needs data ordered by partition and sequence keys. Large sorts consume memory and can spill; one large entity history can dominate runtime. Multiple LAG calls with the same specification can often share processing, while differences in expression do not necessarily require a new sort. Differences in partition, direction, collation, or null ordering may.
| Signal | Mechanism | Evidence |
|---|---|---|
| Sort spill | Large or wide ordered input | Actual rows, width, spill size |
| Slow single partition | Entity-history skew | Maximum partition size |
| Repeated window work | Incompatible specifications | Compare complete OVER clauses |
Validate LAG with adversarial sequences
- Test a one-row partition, duplicate timestamps, NULL values, and missing dates.
- Confirm the first row's NULL or default behavior.
- Use a stable tie-breaker when exact sequence is knowable.
- Compare previous-row and previous-period requirements explicitly.
- Keep current, prior, delta, and entity key for reconciliation.
- Test zero denominators before calculating percentage change.
- Measure maximum partition size, sort memory, spills, and repeated windows.
- Re-run after late events and corrections alter historical order.
Diagnose a month-over-month spike caused by a missing period
Suppose April growth suddenly doubles. The account has January, February, and April rows but no March row, so LAG compares April with February. Preserve the current and previous dates beside both values; the two-month interval reveals the mechanism. Decide whether to label the metric as change since previous observation or build a monthly spine that returns March as missing.
Do not silently fill March with zero unless zero is a verified business fact. Add the missing-period fixture to regression tests and monitor expected-period completeness before publishing period-over-period metrics.
Inspect the complete LAG query
Paste a sanitized complete statement into the InfiniSynapse SQL Complexity Checker to review windows, joins, filters, CTEs, nested layers, and repeated specifications together. Then verify sequence and runtime with your actual database plan.
Open SQL Complexity CheckerRemove credentials, secrets, personal data, and sensitive literals.SQL LAG frequently asked questions
It returns an expression from a previous ordered row in the same partition.
LAG returns NULL or the optional default because no requested predecessor exists.
No. It means previous row; a calendar spine is needed when periods must be continuous.
Peers with incomplete ordering can be processed in different sequences.
Calculate the prior value once, then subtract it and handle NULL or zero explicitly.
Official LAG documentation
Design LAG pipelines for late events and repeatable history
Production event data usually has more than one clock. Event time records when the business action happened; ingestion time records when the platform received it; processing time records when a job transformed it. Ordering by ingestion time answers a different question from ordering by event time. Store both and choose deliberately. When source clocks can drift, document whether a trusted server timestamp, device sequence, or source version resolves conflicts.
Late events can change historical predecessors. A purchase arriving today with last week's event time may be inserted between two existing rows, changing both its own LAG and the next row's LAG. Incremental models that update only the new row are therefore incomplete. Recompute the affected entity range, include a bounded lookback window, or use a change-data strategy that identifies the next dependent row. The correct method depends on the maximum accepted lateness and cost of revision.
Snapshots add another decision. If every daily snapshot carries the latest balance, LAG compares observations, not transactions. Two identical consecutive balances may still represent valid days, while a missing snapshot may mean pipeline failure rather than no change. Track expected snapshot completeness separately and never infer a zero delta from a missing record. For append-only events, deduplicate source identities before sequencing so retries do not appear as real transitions.
Publish a sequence data contract containing entity key, event key, order tuple, tie policy, accepted lateness, update strategy, and first-row behavior. Monitor duplicate order tuples, out-of-order arrival rate, maximum gap, and the number of historical rows revised per load. Preserve a small entity history with a late correction and equal timestamps as an automated regression fixture. This makes a LAG metric repeatable even as pipelines evolve.
Keep sequence decisions visible
A reviewer should be able to reproduce one complete partition from raw events to final deltas. Save the ordered input, predecessor keys, first-row markers, gaps, and revised rows together. This compact evidence distinguishes a real behavioral change from an ordering, deduplication, or late-arrival defect and shortens future incident response.