For each employee in employee table, find number of direct and indirect bosses in hierarchy

Question

I am given an employee table which looks something like this:

Queries to create sample input.

CREATE TABLE employee(
    empId INTEGER,
    empName VARCHAR(20),
    mgrId INTEGER,
    salary DECIMAL(12,2)
);

INSERT INTO employee VALUES
(1, 'A', 2, 100),
(2, 'B', 4, 150),
(3, 'C', 4, 165),
(4, 'D', 7, 200),
(5, 'E', 6, 210),
(6, 'F', 7, 250),
(7, 'G', 7, 300),
(8, 'H', 6, 170);

Link to SQL Fiddle: http://sqlfiddle.com/#!9/cd4be8

This sample data results in this hierarchy.

Each employee has a direct boss. Also, all the employees who are senior to a given employee are referred to as their indirect bosses. In the given sample, the direct boss of A is B, and C, D, E, F, G, and H are A's indirect bosses. I am required to find two things.

1. For each employee, find the count of all the employees who are senior to that employee (number of direct boss + indirect bosses).

Expected output:

+---------+-------+
| empName | total |
+---------+-------+
| A       |     7 |
| B       |     3 |
| C       |     3 |
| D       |     1 |
| E       |     3 |
| F       |     1 |
| G       |     0 |
| H       |     3 |
+---------+-------+

2. For each employee, find the indirect boss in such a way that indirect boss's salary is at least the twice the salary of the given employee but minimum among the indirect bosses.

Expected output:

+---------+------+
| empName | mgr  |
+---------+------+
| A       | D    |
| B       | G    |
| C       | NULL |
| D       | NULL |
| E       | NULL |
| F       | NULL |
| G       | NULL |
| H       | NULL |
+---------+------+

Explanation: for employee A (salary = 100), the indirect bosses with at least double salary are D (salary = 200), F (salary = 210) AND G (salary = 300). But since D's salary is minimum for D, F and G, the result is D.

It is easy to find the count of an employee's subordinates, but the other way round is something very tricky. Any help/hints would be highly appreciated.

Answer 1

I hope you will find the following answers helpful until a more optimal solution comes up.

The first step creates a new view where the level of the employee is recorded. The "boss", named G in this occasion, has the smallest value 1. While the rank is increasing for each level of employee with the highest been 4 for A. The view is constructed with a hierarchical query as here.

After creating the view, the first question is answered by summing all the employees in the higher rank. This is done for every rank and the join brings the information to the final view.

The second question is answered on a more brute force way. A self-join to produce all the viable combinations of employee and bosses is performed. After that the lines with the minimum boss salary are produced.

--Creating a view with the level of the diagram
CREATE VIEW MyCTEView 
AS 
    WITH my_anchor AS (
        SELECT boss.[empId], boss.[empName], boss.[mgrId], 1 AS EmpLevel
        FROM [MySchema].[dbo].[employee] AS boss
        WHERE boss.[mgrId]=boss.[empId]

        UNION ALL

        SELECT Emp.[empId], Emp.[empName], Emp.[mgrId], EL.EmpLevel+1
        FROM [MySchema].[dbo].[employee] AS Emp
        INNER JOIN my_anchor as EL
        ON Emp.[mgrId] = EL.[empId]
        WHERE Emp.[mgrId]<>Emp.[empId]
        )
    SELECT * FROM my_anchor;


--Answer to the first question
SELECT A.[empName]
      ,temp.direct_and_indirect-1 AS your_total
FROM [MySchema].[dbo].[MyCTEView] AS A
LEFT JOIN (
SELECT [EmpLevel],SUM(direct_subortinates) OVER(ORDER BY [EmpLevel]) AS direct_and_indirect
FROM (SELECT COUNT([mgrId]) AS direct_subortinates,[EmpLevel]
            FROM [MySchema].[dbo].[MyCTEView] GROUP BY [EmpLevel])T) AS Temp
ON Temp.[EmpLevel]=A.[EmpLevel]
ORDER BY A.[empName]


--Answer to the second question. Creating a CTE with all the viable combinations of employee and manager based on criteria.
--Displaying the information for the minimum
WITH cte AS (
SELECT A.[empId] as emId
      ,A.[empName] as emName
      ,A.[salary] as emsalary
      ,A.[EmpLevel] as emLevel
      ,B.[empId] as bossId
      ,B.[empName] as bossName
      ,B.[salary] as bosssalary
      ,B.[EmpLevel] as bossLevel
  FROM [MySchema].[dbo].[MyCTEView] AS A
  INNER JOIN 
  [MySchema].[dbo].[MyCTEView] AS B
  ON A.empId<>B.empId AND A.[EmpLevel]>B.[EmpLevel] AND B.[salary]>=2*A.[salary]
  )
SELECT tb1.emName, tb1.bossName 
FROM cte AS tb1
  INNER JOIN
  (
    SELECT emName, MIN(bosssalary) MinSalary
    FROM cte
    GROUP BY emName
  )tb2
  ON  tb1.emName=tb2.emName
  WHERE tb1.bosssalary=tb2.MinSalary