How to get next step

I have the table request_step with dummy info like bellow

id,request_id,state,name,step_number
*2,1,pending,step a,1
1,1,pending,step b,2
3,1,pending,step c,3
4,1,pending,step d,4
5,2,accepted,step a,1
6,2,accepted,step b,2
*7,2,pending,step c,3
8,2,pending,step d,4
9,7,accepted,step a,1
10,7,accepted,step b,2
11,7,accepted,step c,3
14,7,accepted,step d,4
*13,7,pending,step e,5
12,7,pending,step f,6
15,8,accepted,step a,1
17,8,rejected,step b,2
16,8,pending,step c,3   --> not this one because the previous step is rejected
18,9,accepted,step a,1
19,9,accepted,step b,2

I want to take the next steps(pending state) per request_id and the previous steps must not have any rejected state. I mark an asterisk before line to show which one I want to query

I already have a sql script to return me all the next steps but I can not find a solution to check if the previous steps have rejected

select rs1.id, rs1.step_number, rs0
from (
SELECT min(rs1.step_number) as min_sn, rs1.request_id as rid
    FROM request_step rs1
    where rs1.state = 'pending'
    GROUP BY rs1.request_id
) rs0
left join request_step rs1 on rs1.step_number = rs0.min_sn and rs1.request_id = rs0.rid
order by rs1.request_id

Answer

I think you want to look at lag():

select rs.*
from (select rs.*,
             lag(state) over (partition by request_id order by step_number) as prev_state
      from request_step rs
     ) rs
where state = 'pending' and
      (prev_state is null or prev_state <> 'rejected');

EDIT:

If you want to check all previous steps, then you can do something similar with a cumulative sum:

select rs.*
from (select rs.*,
             lag(state) over (partition by request_id order by step_number) as prev_state,
             sum(case when state = 'rejected' then 1 else 0 end) over (partition by request_id order by step_number) as num_rejects
      from request_step rs
     ) rs
where state = 'pending' and
      num_rejects = 0 and
      (prev_status is null or prev_status <> 'pending');