employees(employee_id, manager_id, employee_name)

For every employee, follow manager_id upwards until it is NULL and return the person at the top, plus how many levels up they are.

Someone with no manager is their own top manager, zero levels up. There are no cycles.

Return employee_id, top_manager_id and levels_up, ordered by employee_id.

A fixed number of self joins only works to a fixed depth, so this is the case for a recursive CTE.

Tables

employees

employee_id  manager_id  employee_name
-----------  ----------  ------------------------
1            NULL        Root
2            1           VP
3            2           Director
4            3           Manager
5            4           IC
6            NULL        Founder of a second tree

Expected result

employee_id  top_manager_id  levels_up
-----------  --------------  ---------
1            1               0
2            1               1
3            1               2
4            1               3
5            1               4
6            6               0