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 lines are records with the same ID (column a) and "date1" (column c). ID duplication is due to an improper operation in an array column that may have a different size or number of elements. The most unnecessary operation leads to column b.
Now I want to get the lead date of the next ID "date2". The query I wrote prints "date 1" for the first record, but I really want to get "date 2". I'm not interested in the static solution to this problem, which is a guide (c, 2) (order through a), but how to get the guidance of a window of dynamic size.
date2 date2 NULL