insert into t values

(400,300),

(500,300),

(300,100),

(200,100),

(100,50),

(90,50),

(50,40),

(40,30),

(20,10),

(30,10),

(25,15),

(15,5);

-- h (Hierarchy) is computed recursively. Each step gets its level (which will help order the groups later)

h as

(

-- Head teachers are those who are not student either.

select distinct TeacherId as id, TeacherId as HeadTeacherId, 0 hlevel from t where not exists (select 1 from t UberTeacher where t.TeacherId = UberTeacher.StudentId)

union all

select StudentId, HeadTeacherId, hlevel + 1

from h join t on t.TeacherId = h.id

),

heads as

(

select HeadTeacherId id, string_agg(id, ',') within group (order by hlevel desc, id desc) WholeGroup

from h

group by HeadTeacherId

)

select h.id, HeadTeacherId, WholeGroup

from h join heads on h.HeadTeacherId = heads.id;