Considering the following simple example:
select lead(c) over (partition by a order by a) from (values (1,'1','date1'), (1,'2','date1'), (2,null,'date2') ) t(a,b,c)
The first two rows are records with the same ID (column a) and "date1" (column c). The ID duplication exists due to an unnest operation made in an array column that might have different sizes or number of elements. The unnest operation results in column b.
Now, I want to get the lead date of the next ID, "date2". The query I wrote outputs "date 1" for the first record, but I actually want to get "date 2". I'm not interested in the static solution to this problem, which would be lead(c,2) over (order by a), but on how to get the lead of a dynamic sized window.
date2 date2 NULL