By using SQL Window Functions, how can I get the lead of the last row in a partition? 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. 

Expected output:

date2
date2
NULL
You can specify a window that excludes the current group:

SELECT first_value(c) OVER w
FROM (VALUES
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) AS t(a,b,c)
WINDOW w AS (
   ORDER BY a
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP
);

 first_value 
-------------
 date2
 date2

(3 rows)