title: For each employee in employee table, find number of direct and indirect bosses in hierarchy
body: <p>I am given an employee table which looks something like this:</p>
  
  <p><a href="https://i.stack.imgur.com/X4gcK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X4gcK.png" alt="enter image description here"></a></p>
  
  <p>Queries to create sample input.</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Link to SQL Fiddle: <a href="http://sqlfiddle.com/#!9/cd4be8" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/cd4be8</a></p>
  
  <p>This sample data results in this hierarchy.</p>
  
  <p><a href="https://i.stack.imgur.com/bOm4g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bOm4g.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <ol>
  <li>For each employee, find the count of all the employees who are senior to that employee (number of direct boss + indirect bosses). </li>
  </ol>
  
  <p>Expected output:</p>
  
  <pre><code>+---------+-------+
  | empName | total |
  +---------+-------+
  | A       |     7 |
  | B       |     3 |
  | C       |     3 |
  | D       |     1 |
  | E       |     3 |
  | F       |     1 |
  | G       |     0 |
  | H       |     3 |
  +---------+-------+
  </code></pre>
  
  <ol start="2">
  <li>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. </li>
  </ol>
  
  <p>Expected output:</p>
  
  <pre><code>+---------+------+
  | empName | mgr  |
  +---------+------+
  | A       | D    |
  | B       | G    |
  | C       | NULL |
  | D       | NULL |
  | E       | NULL |
  | F       | NULL |
  | G       | NULL |
  | H       | NULL |
  +---------+------+
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>