Hey,
when using a for you implicitly use a cursor (I think),
so this is the same, use FOR if you like it more.
It should be *very* fast to write !
As I wrote, relational algebra can handle it, but it is not practically feasible :
If you just execute 3 times the query I wrote, you will have your answer.
It is 3 times because the biggest sequence is A A A A.
That's the problem, your number of execution depends on the max size of sequence.
The problems boils down to this : the answer for one row depends on the answer of the previous row, the row before , etc.
You could succeed with ordering by id in a windows function, and in this window function order by new_id and putting null to the end, but such nested windows functions calls are not allowed.
Nevertheless if you find something purely relational please keep me posted !
Cheers,
Rémi-C