How to get cumulative sum of values in each level of Oracle hierarchical query?

Question

This question is in continuation to my previous question on hierarchical query, where Im looking for cumulative sum of values at each level of the hierarchy.

For a schema like the following:

drop table t1 purge;

create table t1(en varchar2(10),bug number, mgr varchar2(10));
insert into t1 values('z',901,'x');
insert into t1 values('z',902,'x');
insert into t1 values('z',903,'x');
insert into t1 values('a',101,'z');
insert into t1 values('a',102,'z');
insert into t1 values('a',103,'z');
insert into t1 values('a',104,'z');
insert into t1 values('b',201,'a');
insert into t1 values('b',202,'a');
insert into t1 values('b',203,'a');
insert into t1 values('c',301,'z');
insert into t1 values('c',302,'z');
insert into t1 values('c',303,'z');
insert into t1 values('d',301,'c');
insert into t1 values('d',302,'c');
commit;

Demo here.

The output I'm expecting is :

MGR  EN   EN_BUG_COUNT  CUMULATIVE_BUG_COUNT    LEVEL
x    null         null                    15        0
x    z               3                    12        1
z    a               4                     7        2
a    b               3                     3        3
z    c               3                     5        2
c    d               2                     2        4

I have spent lot of time and found following links, but the queries they had used is very hard to comprehend:

1. Oracle hierarchical queries data
2. How to sum by levels in Hierarchical (tree)? in Oracle
3. Getting counts at each level of a hierarchical query

Answer 1

Group the bugs and then use a hierarchical query to find the levels and use a correlated hierarchical query to sum the values for descendants:

WITH grouped_bugs (en, en_bug_count, mgr) AS (
  SELECT en,
         COUNT(*),
         mgr
  FROM   t1
  GROUP BY
         en, mgr
)
SELECT mgr,
       en,
       en_bug_count,
       ( SELECT SUM(en_bug_count)
         FROM   grouped_bugs i
         START WITH i.en = g.en
         CONNECT BY PRIOR en = mgr ) AS cumulative_bug_count,
       LEVEL
FROM   grouped_bugs g
START WITH mgr = 'x'
CONNECT BY PRIOR en = mgr
ORDER SIBLINGS BY en;

Which, for the sample data, outputs:

MGR	EN	EN_BUG_COUNT	CUMULATIVE_BUG_COUNT	LEVEL
x	z	3	15	1
z	a	4	7	2
a	b	3	3	3
z	c	3	5	2
c	d	2	2	3

fiddle