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, 2, 3 FROM DUAL UNION ALL
SELECT 4, 3, 4 FROM DUAL UNION ALL
SELECT 5, 4, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;
6 rows affected

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


ID
PARENT_ID
COUNT
COUNT_PRODUCT


1
null
1
1

2
1
2
2

3
2
3
6

4
3
4
24

5
4
5
120

6
5
6
720