Find the closest manager with 2x salary using hierarchy - PostgresSql

user_18042346 0
Viewed 27

I have a table with employeeid,bossid,salary. I have created a hierarchy of all the direct and indirect managers of every employee.

employeeid | bossid | salary | hierarchy
20          NULL     10000       0
10           20       4500       1
50           10       3000       2
70           10        1500      2
40           50        1500      3
60           70        2000      3
30           50        1501      3

Manager = boss
Indirect manager = direct boss OR Boss of Boss
So for employee 30, boss = 50 and indirect = 50, 10 , 20

I want for each employee to find that employee's lowest ranked indirect boss in the hierarchy who earns at least twice as much as the employee.

This is the query I have used to create the hierarchy. I do not know how to proceed

with recursive EmpMgrCTE
as
(SELECT id,name,boss_id,salary, 0 as EmployeeLevel
from employees a
where boss_id is null
union all
select emp.id,emp.name,emp.boss_id,emp.salary,mgr.EmployeeLevel + 1 as EmployeeLevel
from employees emp
inner join EmpMgrCTE mgr
on emp.boss_id = mgr.id
)
select * 
from EmpMgrCTE emp
postgresql sql
1 Answers

Instead of starting the recursion with the top (bossid is null), it can start with all others.

In the recursion it's possible to put the starting id & salary in extra columns which won't change through the recursive loop.

In this example the loop stops as soon a much higher boss salary was found.

WITH RECURSIVE EmpCTE AS
(
    SELECT emp.employeeid, emp.salary, emp.bossid
    , 0 as Level
    , emp.employeeid AS BaseEmployeeId
    , emp.salary AS BaseEmployeeSalary
    FROM employees emp
    WHERE bossid IS NOT NULL
    
    UNION ALL
    
    SELECT mgr.employeeid, mgr.salary, mgr.bossid
    , cte.Level + 1
    , cte.BaseEmployeeId
    , cte.BaseEmployeeSalary
    FROM EmpCTE cte
    JOIN employees mgr
      ON mgr.employeeid = cte.bossid
    WHERE BaseEmployeeSalary*2 > cte.salary
)
SELECT 
  BaseEmployeeId AS EmployeeId
, BaseEmployeeSalary AS EmployeeSalary
, Level
, employeeid AS BossId
, salary AS BossSalary
FROM EmpCTE emp
WHERE BaseEmployeeSalary*2 <= salary
ORDER BY BaseEmployeeId

employeeid | employeesalary | level | bossid | bosssalary
---------: | -------------: | ----: | -----: | ---------:
        10 |           4500 |     1 |     20 |      10000
        30 |           1501 |     2 |     10 |       4500
        40 |           1500 |     1 |     50 |       3000
        50 |           3000 |     2 |     20 |      10000
        60 |           2000 |     2 |     10 |       4500
        70 |           1500 |     1 |     10 |       4500

db<>fiddle here

user_40034190
Related