SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE org(id TEXT, boss_id TEXT, date_interest TEXT);
✓
INSERT INTO org (id, boss_id, date_interest) VALUES
 (1, NULL, 1), (2, 1, 1), (3, 1, 1),
 (1, NULL, 2), (2, 1, 2), (3, 1, 2), (4, 2, 2),
 (1, NULL, 3), (2, 1, 3), (3, 1, 3), (4, 2, 3), (5, 4, 3)
✓
WITH RECURSIVE supervisor_of(id, boss_id, date_interest) AS (
  SELECT org_1.id, org_1.boss_id, org_1.date_interest
  FROM org org_1
  UNION
  SELECT so.id, org_1.boss_id, org_1.date_interest
  FROM org org_1 JOIN supervisor_of so
  ON so.boss_id = org_1.id AND so.date_interest = org_1.date_interest
)
SELECT *, COUNT(*) AS level
FROM supervisor_of
GROUP BY date_interest, id
ORDER BY date_interest, id


id
boss_id
date_interest
level


1
null
1
1

2
1
1
2

3
1
1
2

1
null
2
1

2
1
2
2

3
1
2
2

4
2
2
3

1
null
3
1

2
1
3
2

3
1
3
2

4
2
3
3

5
4
3
4

id	boss_id	date_interest	level
1	null	1	1
2	1	1	2
3	1	1	2
1	null	2	1
2	1	2	2
3	1	2	2
4	2	2	3
1	null	3	1
2	1	3	2
3	1	3	2
4	2	3	3
5	4	3	4