SELECT
 MAX(CHILD_END_TIME) AS GROUP_END_TIME --shis laiks apzimee grupas saakuma laiku
,(SELECT EVV.START_TIME FROM EVENTS EVV WHERE EVV.ID = TOP_PARENT) AS GROUP_START_TIME --shis laiks apziimee grupas beigu laiku
,( MAX(CHILD_END_TIME) - (SELECT EVV.START_TIME FROM EVENTS EVV WHERE EVV.ID = TOP_PARENT) )*24 AS GROUP_LASTING_TIME --shis ir pavaditais laiks stundaas grupaa
FROM
(
    SELECT
            PERSON_ID
           ,EVENT_ID
           ,CHILD_END_TIME
           ,TO_NUMBER(SUBSTR( SYS_CONNECT_BY_PATH(EVENT_ID,'/')
                             ,2
                             ,(CASE WHEN INSTR(SYS_CONNECT_BY_PATH(EVENT_ID, '/'),'/',2)=0 THEN 100
                               ELSE INSTR(SYS_CONNECT_BY_PATH(EVENT_ID, '/'),'/',2)-2 END))) as TOP_PARENT
           -- shis izsaukumsieguust datus formaataa /level1parent/level2parent/level3parent/... un no vinjas dabon aaraa top level parentu - level1parent
      FROM
      (
            SELECT
                 EFE.person_id as PERSON_ID
                ,EV.id as EVENT_ID
                ,EV.END_TIME AS CHILD_END_TIME
                -- shis case atgriezh parent_id balstoties uz ORACLE funkciju LAG, kura atgriezh tuvaako ierakstu peec noraadiitajiem paramtetriem
                -- vai arii atgtriezh NULL ja iespeejamais parents  parents ir taalaak kaa 10 stundas
                  ,CASE
                          WHEN (EV.START_TIME - LAG(EV.END_TIME) OVER (ORDER BY EV.START_TIME)< 10/24) THEN LAG(EV.id) OVER (ORDER BY EV.START_TIME)
                          ELSE NULL
                          END as PARENT_ID
            FROM
                  EMPLOYEES_FOR_EVENT EFE,
                  EVENTS EV
            WHERE
            EFE.EVENT_id = EV.id
            AND
            EFE.person_id = 146
    )
    -- savstarpeejo sakariibu noraadiishana
    START WITH PARENT_ID is Null
    CONNECT BY PRIOR EVENT_ID=PARENT_ID
)
GROUP BY TOP_PARENT
;