I have some commnet about how to keep track at each row. How does it look when we use :

ROW_NUMBER() OVER(PARTITION BY(Occupation) ORDER BY(Name))

This is window function that automatically indexing each row follow our condition, it contain the same mechanism when we use

set @r1=0 ... case .... @r1 := @r1 + 1

Since I see that we hasn't nessecery user variable here, I think we can simplify and avoid to use user variable where it really doesn't ineeded