select name, time, stat, dense_rank() over(partition by name, stat order by flag desc) id, row_number() over(partition by name, stat, flag order by time) ordr from (select t.*, rn1 - nvl(lag(rn2) over(partition by name order by time), 0) flag from (select name, time, stat, row_number() over(partition by name, stat order by time) rn1, row_number() over(partition by name order by time) rn2 from tab) t) order by name, stat, time
time,
stat,
dense_rank() over(partition by name, stat order by flag desc) id,
row_number() over(partition by name, stat, flag order by time) ordr
from (select t.*,
rn1 - nvl(lag(rn2) over(partition by name order by time), 0) flag
from (select name,
time,
stat,
row_number() over(partition by name, stat order by time) rn1,
row_number() over(partition by name order by time) rn2
from tab) t)
order by name, stat, time