LEAD function for next row access in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the next salary value using LEAD function.

SQL

SELECT employee_id, salary, LEAD(salary) OVER (ORDER BY employee_id) AS next_salary FROM employees WHERE employee_id = [1];

Drag options to blanks, or click blank then click option'

A10

B5

C'10'

D'5'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric values in WHERE clause.

Confusing LEAD with LAG function.

The LEAD function is used to access the next row's salary ordered by employee_id. The WHERE clause filters for employee_id = 5 (without quotes because employee_id is numeric).

2fill in blank

medium

Complete the code to get the next order date for each customer ordered by order_date.

SQL

SELECT customer_id, order_date, LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY [1]) AS next_order_date FROM orders;

Drag options to blanks, or click blank then click option'

Aorder_id

Bcustomer_id

Corder_date

Dorder_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by customer_id instead of order_date.

Using a column unrelated to date for ordering.

The LEAD function needs to order rows by order_date within each customer partition to get the next order date.

3fill in blank

hard

Fix the error in the LEAD function usage to get the next product price ordered by product_id.

SQL

SELECT product_id, price, LEAD(price [1]) OVER (ORDER BY product_id) AS next_price FROM products;

Drag options to blanks, or click blank then click option'

A, 1

B(1)

C, 2

D(2)

Attempts:

3 left

💡 Hint

Common Mistakes

Using parentheses instead of comma for offset argument.

Omitting the offset argument.

The LEAD function takes the offset as a second argument separated by a comma, so the correct syntax is LEAD(price, 1).

4fill in blank

hard

Fill both blanks to get the next and previous salaries for each employee ordered by employee_id.

SQL

SELECT employee_id, salary, LEAD(salary, [1]) OVER (ORDER BY employee_id) AS next_salary, LAG(salary, [2]) OVER (ORDER BY employee_id) AS previous_salary FROM employees;

Drag options to blanks, or click blank then click option'

A1

B2

C0

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 as offset which returns the current row value.

Using NULL which is invalid as offset.

Both LEAD and LAG functions use 1 as the offset to get the next and previous row values respectively.

5fill in blank

hard

Fill all three blanks to get the next salary, defaulting to 0 if none, ordered by employee_id.

SQL

SELECT employee_id, salary, LEAD(salary, [1], [2]) OVER (ORDER BY [3]) AS next_salary FROM employees;

Drag options to blanks, or click blank then click option'

A1

B0

Cemployee_id

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary for ORDER BY instead of employee_id.

Omitting default value causing NULL results.

The LEAD function uses 1 as offset, 0 as default value if no next row, and orders by employee_id.