PostgreSQL - Recursive query with hierarchy levels

 CREATE TABLE public.employees (

id int8 NOT NULL,

"name" varchar(50) NULL,

manager_id int8 NULL

);

insert into public.employees(id, name, manager_id) values(1, 'michelle', null);

insert into public.employees(id, name, manager_id) values(2, 'joy', 1);

insert into public.employees(id, name, manager_id) values(3, 'carlos', 2);

insert into public.employees(id, name, manager_id) values(4, 'jane', 3);

insert into public.employees(id, name, manager_id) values(5, 'miguel', 3);

insert into public.employees(id, name, manager_id) values(6, 'josh', 1);

with recursive subordinates as (

-- Anchored element, the person to get all direct

-- and indirect reports to

select 

a.id, a.name, a.manager_id, cast(null as int8) as super_manager_id

, cast(1 as int8) as level

from 

public.employees as a

where 

a.id = 2

-- Recursive elements, all other employees

union -- all 

select 

e.id, e.name, e.manager_id, s.manager_id as super_manager_id

, s.level + 1 as level

from 

public.employees as e 

inner join subordinates as s

on s.id = e.manager_id 

)

select

* 

from 

subordinates

;