How to get a value from previous and next row for each row in BigQuery?

I have a table in BigQuery with the following fields (and many others but not relevent):

• ID
• Timestamp

For example:

ID	Timestamp
1	2021-04-26 14:57:24.292 UTC
4	2019-09-23 19:07:45.002 UTC
1	2020-05-12 04:34:27.475 UTC
5	2021-03-19 14:57:24.292 UTC
4	2020-12-21 16:53:24.292 UTC
1	2019-02-24 13:27:56.924 UTC

I want to get the Start_Date and the End_Date of each row and grouped by ID when the Start_Date is the Date of the Timestamp and the End_Date is the Date of the Timestamp of the following row when it is grouped by ID and ordered by Timestamp.

For example:

ID	Start_Date	End_Date
1	2021-04-26	NULL
4	2019-09-23	2020-12-21
1	2020-05-12	2021-04-26
5	2021-03-19	NULL
4	2020-12-21	NULL
1	2019-02-24	2020-05-12

Or in an ordered way:

ID	Start_Date	End_Date
1	2019-02-24	2020-05-12
1	2020-05-12	2021-04-26
1	2021-04-26	NULL
4	2019-09-23	2020-12-21
4	2020-12-21	NULL
5	2021-03-19	NULL

How can I do this in StandardSQL ?

Answer

Use lead():

select t.*,
       date(timestamp) as start_date,
       date(lead(timestamp) over (partition by id order by timestamp)) as end_date
from t;