SQLite 3.27

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

CREATE TABLE ecosystems (
  slug           VARCHAR(32),
  parent_slug    VARCHAR(32),
  full_slug      VARCHAR(32),
  score          INT
)
✓
INSERT INTO ecosystems VALUES ('aaa', null,  'aaa'        , 1);
✓
INSERT INTO ecosystems VALUES ('bbb', 'aaa', 'aaa/bbb'    , 2);
✓
INSERT INTO ecosystems VALUES ('ccc', 'bbb', 'aaa/bbb/ccc', 4);
✓
INSERT INTO ecosystems VALUES ('ddd', null,  'ddd'        , 8);
✓
INSERT INTO ecosystems VALUES ('eee', 'ddd', 'ddd/eee'    , 16);
✓
INSERT INTO ecosystems VALUES ('fff', null,  'fff'        , 32);
✓
SELECT * FROM ecosystems


slug
parent_slug
full_slug
score


aaa
null
aaa
1

bbb
aaa
aaa/bbb
2

ccc
bbb
aaa/bbb/ccc
4

ddd
null
ddd
8

eee
ddd
ddd/eee
16

fff
null
fff
32


WITH
  tree AS
(
  SELECT
   slug   AS base_slug,
   slug   AS current_slug,
   score  AS score
  FROM
   ecosystems

  UNION ALL

  SELECT
    t.base_slug,
    e.slug,
    e.score
  FROM
    tree        t
  INNER JOIN
    ecosystems  e
      ON e.parent_slug = t.current_slug
)
SELECT
  base_slug    AS slug,
  SUM(score)   AS total_score
FROM
  tree
GROUP BY
  base_slug
ORDER BY
  base_slug


slug
total_score


aaa
7

bbb
6

ccc
4

ddd
24

eee
16

fff
32


SELECT
  e.slug,
  SUM(m.score)   AS total_score
FROM
  ecosystems    e
INNER JOIN
  ecosystems    m  -- members
    ON '/' || m.full_slug || '/' LIKE '%/' || e.slug || '/%'
GROUP BY
  e.slug
ORDER BY
  e.slug


slug
total_score


aaa
7

bbb
6

ccc
4

ddd
24

eee
16

fff
32


WITH
  tree AS
(
  SELECT
    SUBSTR(full_slug || '/', 1, INSTR(full_slug || '/', '/')-1)   AS slug,
    SUBSTR(full_slug || '/',    INSTR(full_slug || '/', '/')+1)   AS path,
    score
  FROM
    ecosystems

  UNION ALL

  SELECT
    SUBSTR(path, 1, INSTR(path, '/')-1)   AS slug,
    SUBSTR(path,    INSTR(path, '/')+1)   AS path,
    score
  FROM
    tree
  WHERE
    tree.path <> ''
)
SELECT
  slug,
  SUM(score)    AS total_score
FROM
  tree
GROUP BY
  slug
ORDER BY
  slug


slug
total_score


aaa
7

bbb
6

ccc
4

ddd
24

eee
16

fff
32

`slug`	`parent_slug`	`full_slug`	`score`
`aaa`	`null`	`aaa`	`1`
`bbb`	`aaa`	`aaa/bbb`	`2`
`ccc`	`bbb`	`aaa/bbb/ccc`	`4`
`ddd`	`null`	`ddd`	`8`
`eee`	`ddd`	`ddd/eee`	`16`
`fff`	`null`	`fff`	`32`

`slug`	`total_score`
`aaa`	`7`
`bbb`	`6`
`ccc`	`4`
`ddd`	`24`
`eee`	`16`
`fff`	`32`

`slug`	`total_score`
`aaa`	`7`
`bbb`	`6`
`ccc`	`4`
`ddd`	`24`
`eee`	`16`
`fff`	`32`

`slug`	`total_score`
`aaa`	`7`
`bbb`	`6`
`ccc`	`4`
`ddd`	`24`
`eee`	`16`
`fff`	`32`