Formula help: randomly assign students to groups based in criteria

//a1 1,a2 2,a3 2，a4 3 like college entry？

select * from assign_students_to_activities;

create temp table aa as 

select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a1');

create temp table aa2 as 

select 'a1' assign,* from aa where rank<=1;

select * from aa2;

//second round

create temp table bb as 

select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a2') and student not in (select student from aa2);

create temp table bb2 as 

select 'a2' assign,* from bb where rank<=2;

select * from bb2;

//3

create temp table cc as 

select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a3') and student not in (

select student from aa2

union all 

select student from bb2

);

create temp table cc2 as 

select 'a3' assign,* from cc where rank<=2;

select * from cc2;

//4

create temp table dd as 

select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a4') and student not in (select student from aa2

union all 

select student from bb2 union all select student from cc2);

select 'a4' assig

//a1 1,a2 2,a3 2，a4 3
select * from assign_students_to_activities;
create temp table aa as 
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a1');
create temp table aa2 as 
select 'a1' assign,* from aa where rank<=1;
select * from aa2;
//second round
create temp table bb as 
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a2') and student not in (select student from aa2);
create temp table bb2 as 
select 'a2' assign,* from bb where rank<=2;
select * from bb2;
//3
create temp table cc as 
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a3') and student not in (
select student from aa2
union  all 
select student from bb2
);
create temp table cc2 as 
select 'a3' assign,* from cc where rank<=2;
select * from cc2;
//4
create temp table dd as 
select row_number() over () rank,* from assign_students_to_activities where instr(activities,'a4') and student not in (select student from aa2
union  all 
select student from bb2 union all select student from cc2);
select 'a4' assign,* from dd where rank<=3;

n,* from dd where rank<=3;