Get previous row value based on a timestamp for matching IDs

Question

I have a table about shipping that has information about the arrival (country and date) to a port. Now I need to extract the country where it departed from using the previous row entries. The table looks like this

ID	CountryArrival	DateArrival
1	BE	1-1-2022
2	US	1-1-2022
1	NL	2-1-2022
2	IT	4-1-2022
1	PT	5-1-2022

I want to obtain the departure for each ID based on the previous ArrivalDate so it would look like this

ID	CountryArrival	DateArrival	DeparturePort
1	BE	1-1-2022	NULL
2	US	1-1-2022	NULL
1	NL	2-1-2022	BE
2	IT	4-1-2022	US
1	PT	5-1-2022	NL

I can obtain the previous Country based only on DateArrival with:

select 
 pc.*,
    lag(pc.CountryArrival) over (order by DateArrival) as DeparturePort
from shipping pc
where pc.DateArrival is not null;

Any idea how to get the previous arrival for matching IDs?

Answer 1

You need to PARTITION BY the ID column.

 lag(pc.CountryArrival) over (PARTITION BY ID order by DateArrival) as DeparturePort