If value_expression returns a NULL value where the preceding or following row, as determined by the specified offset_value, is within the scope of the window group, LAG or LEAD ignores the NULL value.
LAG or LEAD then continues searching for the non-NULL value_expression in the preceding or following row, which may be far from the current row but within the scope of the window group. The search terminates at the window boundaries:
- For LAG, the search terminates at the first row of the window group.
- For LEAD, the search terminates at the last row of the window group.
At the end of the search, LAG or LEAD returns default_value_expression if no non-NULL value_expression is found.
If the preceding or following row is outside the scope of the window group, LAG or LEAD returns default_value_expression.
If the optional NULL clause is not specified, the default option is RESPECT NULLS.