I have a table like this

Id status date 
1 a 2022-01-01 
1 b 2022-02-01 
1 c 2022-03-01 
1 d 2022-04-01 
2 c 2022-01-01 
2 b 2022-02-01 
2 a 2022-03-01 
2 g 2022-04-01 

The data is ordered by id & date. I’d like to get

Id date  
1 2022-03-01 
2 2022-03-01 

where the date is the one that follows immediately after the status b. Or what is the same minimum date among the statuses following status b and the statuses are not sequential

CodePudding user response：

select   id
        ,min(date) as date
from     (
         select  id
                ,case status when 'b' then lead(date) over(partition by id order by date) end as date
         from    t
         ) t
group by id

Fiddle