Window Functions - Value Access Functions

Question 1

What is the primary advantage of using LAG() instead of a self-join to retrieve previous row values?**

A.

A) LAG() returns more columns

B.

B) LAG() automatically filters NULL values

C.

C) LAG() is more readable and efficient, avoiding the complexity of self-joins

Explanation: LAG() simplifies comparative analysis by directly accessing previous row values without requiring complex self-join logic, making queries cleaner and potentially more performant.

D.

D) LAG() works with all data types without conversion

Question 2

What does the **LEAD()** window function primarily retrieve from an ordered set of rows?

A.

A) The previous row's value

B.

B) The next row's value

Explanation: **LEAD()** accesses the value from the subsequent row in the window defined by the **OVER** clause.

C.

C) The current row's value

D.

D) The average of surrounding rows

Question 3

What value does **LEAD()** return for the last row in a window?

A.

A) The first row's value

B.

B) Zero

C.

C) The maximum value in the partition

D.

D) NULL

Explanation: The last row in the window has no subsequent row, so **LEAD()** returns **NULL**.

Question 4

What is the primary difference between **FIRST_VALUE()** and **LAG()** in the context of window functions?

A.

A) **FIRST_VALUE()** returns the first row in the partition; **LAG()** returns the previous row relative to the current row

Explanation: **FIRST_VALUE()** consistently returns the same value (the first row's value) for every row in its window, while **LAG()** returns different values—specifically the value from the previous row relative to each current row. Both support partitioning and ordering.

B.

B) **FIRST_VALUE()** requires a frame clause; **LAG()** does not

C.

C) **FIRST_VALUE()** can be used with **PARTITION BY**, but **LAG()** cannot

D.

D) **LAG()** is older and less efficient than **FIRST_VALUE()**

Question 5

What happens when you apply **FIRST_VALUE()** without an **ORDER BY** clause in the window specification?

A.

A) The function returns an error

B.

B) The function returns NULL for all rows

C.

C) The function operates on an undefined row order, producing unpredictable results

Explanation: Without **ORDER BY**, the window function lacks a defined ordering, so which row is considered "first" becomes ambiguous. This leads to unpredictable or database-specific behavior. While some databases may not error, the result is meaningless

D.

D) The function treats all rows as equally valid and returns a random value

Question 6

When using **NTH_VALUE()**, what determines which value is considered the "nth" value?

A.

A) The position of the row in the original table

B.

B) The ordering specified in the **ORDER BY** clause of the window function

Explanation: The **ORDER BY** clause in the window specification determines the sequence in which rows are evaluated. **NTH_VALUE()** counts positions based on this order, not the original table structure or any other factor.

C.

C) The numerical suffix in the column name

D.

D) The number of partitions defined by **PARTITION BY**

📝 Window Functions - Value Access Functions

Quiz Complete!