LEAD

Why interviewers love this: LEAD looks tiny, but it checks whether you can reason about ordered data row by row instead of only thinking in groups.

Question: What does LEAD do in SQL?

Answer: LEAD is a window function that returns a value from a following row in the same ordered result set. You usually pair it with OVER (PARTITION BY ... ORDER BY ...) so the database knows what counts as the next row. If there is no next row, it returns NULL unless you give a default value.

Interview-Ready Answer: I use LEAD when I want to look one row ahead in an ordered set, like comparing today’s value to tomorrow’s or finding the next event time. I define the order in the OVER clause, optionally split the data with PARTITION BY, and then LEAD returns the next row’s value, or NULL if there is no next row. A nice detail is that a default value only replaces a missing row, not a next row whose column value is actually NULL.

Detailed Explanation:

What it is

LEAD is a window function, which means it computes a value using the current row plus nearby rows while keeping every row in the result. A window is just the ordered set of rows visible to the function. Unlike GROUP BY, it does not merge rows into one summary row.

How it works under the hood

1. The database splits the data into partitions when you use PARTITION BY. Each partition is handled separately, like its own mini table.
2. Inside each partition, rows are sorted by the ORDER BY list. This step matters because LEAD only makes sense when the row order is known.
3. The engine marks the current row and then moves forward by the offset. The default offset is 1, so plain LEAD(x) means next row.
4. It reads the target row’s expression value. If that row exists but the expression is NULL, the result is still NULL.
5. If the target row does not exist, the function returns the default value if you supplied one; otherwise it returns NULL.

One useful mental detail: the window frame is the exact slice of rows a window function can see. That matters for aggregates like SUM() OVER, but for LEAD, the key idea is simply the ordered partition and the offset ahead.

When and why to use it

• Compare consecutive days, events, prices, or statuses.
• Find gaps, such as time until the next event or next payment.
• Detect changes, like when a customer’s plan or state changes.
• Build cleaner queries than self joins when you only need the next row.

LEAD vs alternatives

LEAD is usually clearer than a self join because you do not have to manually match each row to the next one. It is also symmetrical with LAG, which looks backward instead of forward.

Performance and edge cases

The expensive part is usually sorting, not the lookup itself. A common mental model is O(n log n) for the sort and then O(n) to walk the rows, so the total is dominated by ordering. On large partitions, the sort can spill to disk if memory is tight; in PostgreSQL, for example, that can happen when the working memory limit is too small. An index on the partition and order columns can help some engines read rows in the needed order and avoid an extra sort.

• If you omit ORDER BY, the idea of a next row is undefined, so results are not reliable.
• If your ORDER BY has ties, add a tie-breaker such as a unique id, or the next row may change between runs.
• Offsets past the end of the partition return NULL or the default value.
• LEAD does not skip NULL values.
• The default argument only applies when the target row is missing, not when the target row exists and its column is NULL.

Real-World Example: Imagine an e-commerce checkout service that stores each customer’s event stream: cart created, payment started, payment succeeded, receipt sent. Analysts use LEAD to compare each event time to the next event time and measure delays between steps.

It works well until a team orders events only by timestamp and two events land in the same second. Then the next row becomes unstable because the order is not fully deterministic. The symptom is weird: one run says a customer moved from payment to receipt in 2 seconds, another run says 9 seconds, and fraud alerts or abandoned-cart emails fire inconsistently. In the logs you may see changing row order for the same user, support tickets about duplicate emails, and dashboard numbers that do not match between refreshes. The fix is to add a stable tie-breaker, such as an event id, to the ORDER BY list.

Follow-up & Tricky Questions:

• Why do we need ORDER BY inside OVER? Because LEAD needs a defined row sequence. Without order, there is no reliable idea of which row comes next.
• What is the difference between LEAD and LAG? LEAD looks forward to a later row; LAG looks backward to an earlier row. They are mirror images and are often used together for change detection.
• What does the third argument do? It is the default value used only when the offset row does not exist. It does not replace a real NULL from the next row.
• How do you make the result deterministic when timestamps tie? Add a second sort key, usually a unique id or sequence number, so the database has one stable next row.
• Can LEAD cross partitions? No. Each partition is isolated, so the last row of one partition never looks into the next partition.
• Tricky: If I omit PARTITION BY, what happens? The entire result set becomes one partition, so LEAD looks ahead across all rows in the whole query result.
• Tricky: Does LEAD skip nulls automatically? No. It returns the next row exactly as stored, even if that value is NULL.
• Tricky: Is the default value used when the next row exists but the value is null? No. The default only applies when the target row is missing, not when the target row contains a null.

Common Mistakes:

• Forgetting ORDER BY. Correction: always define a deterministic order, or the next row is meaningless.
• Assuming the default replaces null values. Correction: it only replaces a missing row beyond the end of the partition.
• Not adding a tie-breaker. Correction: if two rows share the same order key, add a unique column so the next row is stable.
• Confusing LEAD with LAG. Correction: LEAD looks forward, LAG looks backward.

Memory Hook: Think of rows as people in a line: LEAD asks, Who is in front of me? and LAG asks, Who is behind me?

Cheat Sheet:

• LEAD(expr) means the next row’s value.
• PARTITION BY resets the look-ahead inside each group.
• ORDER BY defines what next means.
• Default value applies only when the row is missing.
• Duplicate order keys need a tie-breaker.
• Sorting usually dominates cost; the lookup itself is cheap.

Practice Tasks:

• Take an orders table and add a column showing the next order date per customer.
• Change the query to find the previous row with LAG and compare the output.
• Add a tie-breaker column to an event stream and observe how it changes the next-row result.