SQL> create table t_eli(user_id int, step int, tag varchar2(30));
Table created
SQL> select * from T_ELI;
USER_ID STEP TAG
--------------------------------------- --------------------------------------- ------------------------------
123456 1 0
123456 2 xxx
123456 3 abc
123456 4 right
321654 1 xxx
321654 2 ggg
321654 3 sds
321654 4
321654 5 right
9 rows selected
SQL>
SQL> select *
2 from t_eli
3 where tag = 'right'
4 union all
5 select b.user_id,c.step,c.tag
6 from (
7 select user_id,last_step
8 from (
9 select user_id,tag,lag(step) over(partition by user_id order by step) last_step from t_eli
10 ) a
11 where a.tag = 'right') b,t_eli c
12 where b.user_id = c.user_id
13 and b.last_step = c.step
14 order by 1,2,3
15 /
USER_ID STEP TAG
--------------------------------------- --------------------------------------- ------------------------------
123456 3 abc
123456 4 right
321654 4
321654 5 right
SQL>