Oracle 23c

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

CREATE TABLE table_name (
  id        NUMBER(10,0) PRIMARY KEY,
  parent_id REFERENCES table_name (id),
  count     NUMBER(10,0)
)
INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;
6 rows affected

WITH rsqfc (id, count, count_product) AS (
  SELECT id, count, count FROM table_name WHERE parent_id IS NULL
UNION ALL
  SELECT t.id, t.count, t.count * r.count_product
  FROM   rsqfc r
         INNER JOIN table_name t
         ON (r.id = t.parent_id)
)
SELECT *
FROM   rsqfc;


ID
COUNT
COUNT_PRODUCT


1
1
1

2
2
2

3
3
3

4
4
8

5
5
15

6
6
90