By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table salary (
employee_id int,
amount int
);
insert into salary values
(0, 200),(1, 300),(2, 400),(3, 100),(4, 100),(5, 500),(6, 600),(7, 1000),(8, 1000);
create table employees (
employee_id int,
supervisor_id int
);
insert into employees values
(0, 5),(1, 5),(2, 5),(3, 6),(4, 6),(5, 7),(6, 8),(7, NULL),(8, NULL);
CREATE TABLE
INSERT 0 9
CREATE TABLE
INSERT 0 9
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 c.*, s.amount
from cte c
inner join salary s on s.employee_id = c.employee_id
where c.supervisor_id <> c.employee_id
order by supervisor_id
| supervisor_id | employee_id | amount |
|---|---|---|
| 5 | 2 | 400 |
| 5 | 0 | 200 |
| 5 | 1 | 300 |
| 6 | 3 | 100 |
| 6 | 4 | 100 |
| 7 | 0 | 200 |
| 7 | 5 | 500 |
| 7 | 1 | 300 |
| 7 | 2 | 400 |
| 8 | 6 | 600 |
| 8 | 3 | 100 |
| 8 | 4 | 100 |
SELECT 12
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
| supervisor_id | round |
|---|---|
| 5 | 300.00 |
| 6 | 100.00 |
| 7 | 350.00 |
| 8 | 266.67 |
SELECT 4