Digital_routes_employee

Digital_routes_employee.sql

/*
id,name,salary,boss
10,k,1500,50
50,H,3000,10
10,J,4500,20
20,K,10000,NULL
70,J,1500,10
60,Oliver,2000,70
30,L,1501,50

For each id find boss who earns atleast twice as id 
1st row , id=10 ,earns =1500 , boss 50 earns 3000 (keep id,boss id)
2nd row , id=20 ,earns =3000 , boss 10 earns 4500 which is less than 3*3000 
,therefore take boss of the boss ie 20 , who earns 10,000 keep id ,boss to 20
(keep id,boss id)



Expected result
id,boss
10,20
50,20
20,NULL
30,10
40,50
60,10

*/


WITH temp AS (
    SELECT 
        e.id AS eID,
        e.salary AS eSal,
        d.id AS dID,
        d.salary AS dSal,
        d.boss_id AS dBoss
    FROM employees e
    LEFT JOIN employees d ON e.boss_id = d.id
    ORDER BY e.id
),
temp2 AS (
    SELECT * 
    FROM temp 
    WHERE dSal > 2 * eSal
),
temp3 AS (
    SELECT * 
    FROM temp 
    WHERE dSal < 2 * eSal
),
temp4 AS (
    SELECT *
    FROM employees
    WHERE boss_id IN (SELECT boss_id FROM temp3)
),
temp5 AS (
    SELECT t3.eID, t3.dBoss
    FROM temp3 t3
    LEFT JOIN temp4 t4 ON t3.dBoss = t4.id
)

SELECT eID, dBoss FROM temp5
UNION 
SELECT eID, dID FROM temp2;