How can I get the information from the following row and fill if there is no information?

Question

So I have the following table:

Id     Billing_Date
A00    2020-01-01
A00    2020-02-01
A00    2020-04-01
B91    2020-01-01
B91    2020-03-01
B91    2020-04-01
C11    2020-01-01 

What I want to do is to get the date from the following row and if I do not find this date, then I would fill with the Billing_Date + 60 days. All based on the Id of course. This is what my data should look like in the end:

Id     Billing_Date    Billing_Date_Duo
A00    2020-01-01      2020-02-01
A00    2020-02-01      2020-04-01
A00    2020-04-01      2020-06-01
B91    2020-01-01      2020-03-01
B91    2020-03-01      2020-04-01
B91    2020-04-01      2020-06-01
C11    2020-01-01      2020-03-01

How can I do this? I know I should use something like lead(), but not sure how to do this here and how to combine with case when.

I thought this would work, but it didn't:

select Id, Billing_Date, 
    lead(Billing_Date) over (order by Id) as Billing_Date_Duo

Answer 1

You can use lead() over the correct partition (id) and order by, and coalesce() to assign a default value:

select
    id,
    billing_date,
    coalesce(
        lead(billing_date) over(partition by id order by billing_date),
        billing_date + interval '60 day'
    ) billing_date_duo
from mytable

You can shorten this with the 3-form argument of lag():

select
    id,
    billing_date,
    lead(billing_date, 1, (billing_date + interval '60 day')::date) 
        over(partition by id order by billing_date) billing_date_duo
from mytable

Demo on DB Fiddle