Find date range for every row. LEAD/LAG but with different types/actions

I have a table with following data:

Action   Code    Place        Date
Remove   A       Place 1      2019-03-15
Install  A       Place 2      2019-10-01
Remove   A       Place 2      2022-07-12
Install  A       Place 3      2023-01-01

There are two actions possible. ‘Remove’ and ‘Install’. How can I find Place for Code A for given date? For example for 2015 it will be Place 1. For 2020 it will be Place 2. For 2025 it will be Place 3.

I think the first and the most difficult step is to transform it to table that contains start and end date. Then I can use between function.

Example output:

A   Place   StartDate   EndDate
A   Place 1 1900-01-01  2019-03-15 -- we dont have start date so we can use for ex. 1900
A   Place 2 2019-10-01  2022-07-12
A   Place 3 2023-01-01  3000-01-01 -- we dont have end date so we can use for ex. 3000

I have tried with LEAD/LAG but we have two different actions here so I think it is not the best idea. How could I deal with this problem?

Thanks.

Answer

I understand. The following uses union all to be sure it gets all values.

select code, place, '1900-01-01' as startdate, date as enddate
from (select t.*, row_number() over (partition by code order by date) as seqnum
      from t
     ) t
where seqnum = 1 and action = 'Remove'
union all
select code, place, date as startdate, enddate
from (select t.*,
             lead(date, 1, '3000-01-01') over (partition by code order by date) as enddate
      from t
     ) t
where action = 'Install';

The first subquery handles the case where the data starts with 'Remove'. The second takes all 'installs' and sets up the date to the next record.

Note that this will work even if the places are interleaved in the data (so a code returns to a place after some period of time).

Here is a db<>fiddle.