-1

Problem

I have two tables. employee and salary. For the employee table I have employee_id and supervisor_id.

employee_id supervisor_id
0 5
1 5
2 5
3 6
4 6
5 7
6 8
7 NULL
8 NULL

For the salary table I have amount and employee_id.

employee_id amount
0 200
1 300
2 400
3 100
4 100
5 500
6 600
7 1000
8 1000

Question

For the supervisor with no supervisor (in my case employee id 7 and 8) I want to get the mean of sum of salary of all the employees that are working under them. So, for 7, it would be employee 5,2,1 and 0 and for employee 8 it would be 6,4 and 3.

I have tried using recursive cte but haven't been able to make much progress with it

WITH RECURSIVE supervisor_tree AS (
  SELECT e.id, e.supervisor_id, (SELECT f.amount FROM salary f WHERE f.employee_id= e.id) AS amount
  FROM employees e
  WHERE e.supervisor_id IS NULL -- only top-level supervisors
  UNION ALL
  SELECT e.id, e.supervisor_id, COALESCE(f.weight, 0) AS weight
  FROM employees e
  INNER JOIN salary f ON e.id = f.employee_id
  INNER JOIN supervisor_tree st ON e.supervisor_id = st.id
)
SELECT supervisor_id, weight 
FROM supervisor_tree;
mu_sa
  • 2,685
  • 10
  • 39
  • 58

1 Answers1

1
WITH RECURSIVE employee_paths (employee_id, path) AS
(
  SELECT employee_id, ARRAY[employee_id]
    FROM employees
    WHERE supervisor_id is null
  UNION ALL
  SELECT e.employee_id, path || e.employee_id
    FROM employee_paths AS ep 
    JOIN employees AS e ON e.supervisor_id = ep.employee_id
),
cte as (
  SELECT e.employee_id as supervisor_id, ep.employee_id
  FROM employees e
  INNER JOIN employee_paths ep on e.employee_id =ANY(ep.path)
  order by e.employee_id
)
select supervisor_id, round(AVG(amount),2)
from cte c
inner join salary s on s.employee_id = c.employee_id
where c.supervisor_id <> c.employee_id
group by supervisor_id
order by supervisor_id

The employee_path gets all supervisors of employees, then the second cte to get all the employees that are working under every supervisor.

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29