Posted by As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result.
Here’s a trivial example
SQL> create table t as
2 select rownum x
3 from dual
4 connect by level <= 10;
Table created.
SQL>
SQL> select x, lag(x) over ( order by x ) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
We get null for the first row, because we cannot lag “below” x=1 because there is no such value. That is of course trivially solved with an NVL, for example:
SQL> select x, nvl(lag(x) over ( order by x ),999) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1 999
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
But a quick look at the documentation, shows that you don’t need the “clutter” of NVL, because lag, lead already provide for a default value.
SQL> select x, lag(x,1,999) over ( order by x ) as lag_Test
2 from t;
X LAG_TEST
---------- ----------
1 999
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
10 rows selected.
SQL>
Learning is not a spectator sport 
And, maybe more importantly, with the default you can distinguish between a NULL in the table and a reference outside of the partition.
That’s a mighty fine point, Alex. How long as this feature been there?!
As far as I know since the introduction of LEAG and LAG (if I’m not mistaken, since 8.1.6 EE)
Turns out it was in 8.1.7, when I started. Must have missed it ;p
https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/function.htm#83619