One option could be:
Code:
SQL> select order#, rb initial_revise, nr next_revise
2 from (
3 select order#,
4 revised_by rb,
5 order_date od,
6 lead(revised_by) over (partition by order# order by order_date) nr
7 from orders)
8 where nr is not null
9 order by 1, od;
ORDER# INITIAL_REVISE NEXT_REVISE
---------- -------------------- --------------------
134 user1 user2
134 user2 user3
134 user3 user4
134 user4 user5
135 user3 user4
135 user4 user2
135 user2 user3
135 user3 user6
135 user6 user7
135 user7 user5
10 rows selected.