NTH_VALUE function in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of NTH_VALUE with simple partition

Given the table sales with columns region and amount, what is the output of the following query?

SELECT region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM sales;

SQL

CREATE TABLE sales (region VARCHAR(10), amount INT);
INSERT INTO sales VALUES
('East', 100), ('East', 200), ('East', 300),
('West', 150), ('West', 250);

SELECT region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM sales ORDER BY region, amount;

A [{'region': 'East', 'amount': 100, 'second_amount': 200}, {'region': 'East', 'amount': 200, 'second_amount': 200}, {'region': 'East', 'amount': 300, 'second_amount': 200}, {'region': 'West', 'amount': 150, 'second_amount': 250}, {'region': 'West', 'amount': 250, 'second_amount': 250}]

B [{'region': 'East', 'amount': 100, 'second_amount': 100}, {'region': 'East', 'amount': 200, 'second_amount': 200}, {'region': 'East', 'amount': 300, 'second_amount': 300}, {'region': 'West', 'amount': 150, 'second_amount': 150}, {'region': 'West', 'amount': 250, 'second_amount': 250}]

C [{'region': 'East', 'amount': 100, 'second_amount': 100}, {'region': 'East', 'amount': 200, 'second_amount': 100}, {'region': 'East', 'amount': 300, 'second_amount': 100}, {'region': 'West', 'amount': 150, 'second_amount': 150}, {'region': 'West', 'amount': 250, 'second_amount': 150}]

D [{'region': 'East', 'amount': 100, 'second_amount': null}, {'region': 'East', 'amount': 200, 'second_amount': null}, {'region': 'East', 'amount': 300, 'second_amount': null}, {'region': 'West', 'amount': 150, 'second_amount': null}, {'region': 'West', 'amount': 250, 'second_amount': null}]

💡 Hint

Remember that NTH_VALUE returns the value of the nth row in the window frame, ordered by the ORDER BY clause.

The NTH_VALUE(amount, 2) function returns the second smallest amount within each region partition. For 'East', the amounts ordered are 100, 200, 300, so the second is 200. For 'West', the amounts are 150, 250, so the second is 250. This value is repeated for all rows in the partition.

🧠 Conceptual

intermediate

Understanding frame specification impact on NTH_VALUE

What happens if you run the query below on a table orders with columns customer_id and order_date? Assume multiple orders per customer.

SELECT customer_id, order_date, NTH_VALUE(order_date, 3) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS third_order_date FROM orders;

Which statement is true?

A The function returns the third earliest order date for each customer for all rows.

B The function returns the third order date only if the frame includes at least three rows; otherwise, it returns NULL.

C The function always returns the third order date of the entire table, ignoring partitions.

D The function returns NULL for the first two orders and the third order date starting from the third row onward per customer.

💡 Hint

Consider how the frame defined by ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW changes as the window moves.

The frame grows from the first row to the current row. For the first two rows, the frame has fewer than three rows, so NTH_VALUE returns NULL. Starting from the third row, the frame includes at least three rows, so the third order date in the frame is returned.

📝 Syntax

advanced

Identify the syntax error in NTH_VALUE usage

Which of the following queries will cause a syntax error?

A SELECT NTH_VALUE(salary, 1) OVER (ORDER BY salary) FROM employees;

B SELECT NTH_VALUE(salary, 3) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM employees;

C SELECT NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary) FROM employees;

D SELECT NTH_VALUE(salary, 0) OVER (ORDER BY salary) FROM employees;

💡 Hint

Check the valid range for the second argument of NTH_VALUE.

The second argument to NTH_VALUE must be a positive integer starting from 1. Using 0 is invalid and causes a syntax error.

❓ optimization

advanced

Optimizing NTH_VALUE with large datasets

You have a large transactions table with millions of rows. You want to find the 5th highest transaction amount per customer using NTH_VALUE. Which approach is most efficient?

A Use NTH_VALUE(amount, 5) OVER (PARTITION BY customer_id ORDER BY amount DESC) without frame clause.

B Use NTH_VALUE(amount, 5) OVER (PARTITION BY customer_id ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

C Use a subquery with ROW_NUMBER() to filter the 5th row per customer instead of NTH_VALUE.

D Use NTH_VALUE(amount, 5) OVER (ORDER BY amount DESC) without partitioning.

💡 Hint

Consider performance implications of window functions on large data and alternatives.

Using ROW_NUMBER() in a subquery to filter the 5th row per customer is often more efficient than NTH_VALUE on large datasets because it avoids computing the window function for all rows and allows filtering early.

🔧 Debug

expert

Debugging unexpected NULLs from NTH_VALUE

You run this query:

SELECT employee_id, salary, NTH_VALUE(salary, 3) OVER (PARTITION BY department ORDER BY salary) AS third_salary FROM employees;

But you notice that third_salary is NULL for some employees even though their department has more than three employees. What is the most likely cause?

A The default window frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the frame may not include the third row yet.

B NTH_VALUE always returns NULL if the partition has more than 3 rows.

C The ORDER BY clause is missing in the window function.

D The salary column contains NULL values causing NTH_VALUE to return NULL.

💡 Hint

Think about how the default frame affects which rows are visible to the function at each row.

By default, the frame is from the first row to the current row. For the first and second rows, the frame has fewer than three rows, so NTH_VALUE returns NULL. Only from the third row onward does the frame include the third row, returning a non-NULL value.