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?.
select version();
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table employees(
employee_name VARCHAR(50),
global_team VARCHAR(20),
team VARCHAR(20)
);
insert into employees values
('name_of_head_of_engineering', NULL, 'Engineering'),
('name_1_T1', 'Engineering', 'Team_1'),
('name_2_T1', 'Engineering', 'Team_1'),
('name_3_T1', 'Engineering', 'Team_1'),
('name_4_T1', 'Engineering', 'Team_1'),
('name_5_T1', 'Engineering', 'Team_1'),
('name_2_T2', 'Engineering', 'Team_2'),
('name_1_T2_1', 'Team_2', '2_Team_1'),
('name_2_T2_1', 'Team_2', '2_Team_1'),
('name_1_T2_2', 'Team_2', '2_Team_2'),
('name_2_T2_2', 'Team_2', '2_Team_2'),
('name_3_T2_2', 'Team_2', '2_Team_2');
12 rows affected
SELECT DISTINCT
e.team,
(
WITH RECURSIVE cte AS (
SELECT
ee.team,
ee.global_team
FROM employees ee
WHERE ee.team = e.team
UNION ALL
SELECT
ee.team,
ee.global_team
FROM employees ee
JOIN cte ON ee.global_team = cte.team
)
SELECT COUNT(*) FROM cte WHERE global_team IS NOT NULL
) AS count_employee
FROM employees e
ORDER BY count_employee DESC
| team | count_employee |
|---|---|
| Engineering | 11 |
| Team_2 | 6 |
| Team_1 | 5 |
| 2_Team_2 | 3 |
| 2_Team_1 | 2 |