LEVEL FROM BOTTOM AND POPULATION FROM TOP - ORACLE, Oracle

Language: Layout:

create table TB_POPULATION(ID int, TITLE varchar(20), PARENT_ID int, POPULATION int)\\

insert into TB_POPULATION values
(1,'WORLD',NULL,10)\\
insert into TB_POPULATION values
(2,'AFRICA',1,5)\\
insert into TB_POPULATION values
(3,'ASIA',1,10)\\
insert into TB_POPULATION values
(4,'EUROPE',1,4)\\
insert into TB_POPULATION values
(5,'GERMANY',4,6)\\
insert into TB_POPULATION values
(6,'FRANCE',4,10)\\
insert into TB_POPULATION values
(7,'ITALY',4,4)\\
insert into TB_POPULATION values
(8,'JAPAN',3,6)\\
insert into TB_POPULATION values
(9,'MORROCO',2,1)\\
insert into TB_POPULATION values
(10,'SPAIN',4,9)\\
insert into TB_POPULATION values
(11,'INDIA',3,8)\\
insert into TB_POPULATION values
(12,'PORTUGAL',4,2)\\
insert into TB_POPULATION values
(13,'USA',14,10)\\
insert into TB_POPULATION values
(14,'AMERICA',1,10)\\
insert into TB_POPULATION values
(15,'NEWYORK',13,5)\\

SELECT TOP_TO_BOTTOM.TITLE_ALIAS, TOP_TO_BOTTOM.TOTAL_POPULATION, BOTTOM_TO_TOP.MAX_LEVEL FROM
(SELECT TITLE_ALIAS, SUM(POPULATION) AS "TOTAL_POPULATION" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", POPULATION
      FROM TB_POPULATION 
      CONNECT BY PRIOR ID = PARENT_ID) 
GROUP BY TITLE_ALIAS) "TOP_TO_BOTTOM"
INNER JOIN
(SELECT TITLE_ALIAS, MAX(LEV) AS "MAX_LEVEL" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", LEVEL AS "LEV"
      FROM TB_POPULATION 
      CONNECT BY PRIOR PARENT_ID = ID) 
GROUP BY TITLE_ALIAS) "BOTTOM_TO_TOP"
ON
BOTTOM_TO_TOP.TITLE_ALIAS = TOP_TO_BOTTOM.TITLE_ALIAS
ORDER BY BOTTOM_TO_TOP.MAX_LEVEL\\


absolute service time: 1,46 sec