LEAD function for next row access in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEAD function with default offset

Given the table Sales with columns id and amount, what is the output of this query?

SELECT id, amount, LEAD(amount) OVER (ORDER BY id) AS next_amount FROM Sales ORDER BY id;

SQL

CREATE TABLE Sales (id INT, amount INT);
INSERT INTO Sales VALUES (1, 100), (2, 200), (3, 300);

A [{"id":1,"amount":100,"next_amount":200},{"id":2,"amount":200,"next_amount":300},{"id":3,"amount":300,"next_amount":null}]

B [{"id":1,"amount":100,"next_amount":100},{"id":2,"amount":200,"next_amount":200},{"id":3,"amount":300,"next_amount":300}]

C [{"id":1,"amount":100,"next_amount":null},{"id":2,"amount":200,"next_amount":null},{"id":3,"amount":300,"next_amount":null}]

D [{"id":1,"amount":100,"next_amount":300},{"id":2,"amount":200,"next_amount":null},{"id":3,"amount":300,"next_amount":null}]

💡 Hint

LEAD returns the value of the next row in the order specified.

The LEAD function with default offset 1 returns the amount from the next row ordered by id. For the last row, there is no next row, so it returns null.

❓ query_result

intermediate

LEAD function with offset 2

What is the output of this query on the same Sales table?

SELECT id, amount, LEAD(amount, 2) OVER (ORDER BY id) AS next_2_amount FROM Sales ORDER BY id;

SQL

CREATE TABLE Sales (id INT, amount INT);
INSERT INTO Sales VALUES (1, 100), (2, 200), (3, 300), (4, 400);

A [{"id":1,"amount":100,"next_2_amount":400},{"id":2,"amount":200,"next_2_amount":null},{"id":3,"amount":300,"next_2_amount":null},{"id":4,"amount":400,"next_2_amount":null}]

B [{"id":1,"amount":100,"next_2_amount":200},{"id":2,"amount":200,"next_2_amount":300},{"id":3,"amount":300,"next_2_amount":400},{"id":4,"amount":400,"next_2_amount":null}]

C [{"id":1,"amount":100,"next_2_amount":null},{"id":2,"amount":200,"next_2_amount":null},{"id":3,"amount":300,"next_2_amount":null},{"id":4,"amount":400,"next_2_amount":null}]

D [{"id":1,"amount":100,"next_2_amount":300},{"id":2,"amount":200,"next_2_amount":400},{"id":3,"amount":300,"next_2_amount":null},{"id":4,"amount":400,"next_2_amount":null}]

💡 Hint

Offset 2 means skipping one row and looking two rows ahead.

LEAD(amount, 2) returns the amount value two rows after the current row ordered by id. Rows without a second next row return null.

📝 Syntax

advanced

Identify the syntax error in LEAD usage

Which option contains a syntax error when using the LEAD function?

A SELECT id, LEAD(amount, 1, 0) OVER (ORDER BY id) FROM Sales;

B SELECT id, LEAD(amount, 2) OVER (ORDER BY id) FROM Sales;

C SELECT id, LEAD(amount ORDER BY id) FROM Sales;

D SELECT id, LEAD(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM Sales;

💡 Hint

Check the placement of parentheses and commas in LEAD function.

Option C is missing a comma between the first argument and the ORDER BY clause inside LEAD, which is invalid syntax. LEAD requires the ORDER BY clause in the OVER() clause, not inside the function arguments.

❓ optimization

advanced

Optimizing LEAD usage with partitioning

Given a Sales table with columns region, id, and amount, which query correctly uses LEAD to get the next amount within each region?

A SELECT region, id, amount, LEAD(amount) OVER (PARTITION BY region ORDER BY id) AS next_amount FROM Sales;

B SELECT region, id, amount, LEAD(amount) OVER (ORDER BY id) AS next_amount FROM Sales;

C SELECT region, id, amount, LEAD(amount, 1) OVER (PARTITION BY id ORDER BY region) AS next_amount FROM Sales;

D SELECT region, id, amount, LEAD(amount) FROM Sales GROUP BY region, id, amount;

💡 Hint

Partitioning groups rows before applying LEAD.

Option A partitions rows by region, so LEAD returns the next amount within the same region ordered by id. Option A ignores regions, C partitions incorrectly, and D misuses aggregation.

🧠 Conceptual

expert

Understanding LEAD with default value

What will be the output of the next_amount column for the last row in this query?

SELECT id, amount, LEAD(amount, 1, 999) OVER (ORDER BY id) AS next_amount FROM Sales ORDER BY id;

Assuming the last row has id = 4 and amount = 400.

A NULL

B 999

C 400

D 0

💡 Hint

The third argument in LEAD is the default value if no next row exists.

LEAD(amount, 1, 999) returns the amount from the next row ordered by id. For the last row, since no next row exists, it returns the default value 999 instead of NULL.