(

SELECT 'jsmith', 'Front Desk', timestamp'2020-08-24 08:00:00' , timestamp'2020-08-24 09:30:00' FROM dual UNION ALL

SELECT 'jsmith', 'Phones' , timestamp'2020-08-24 08:15:00' , timestamp'2020-08-24 08:45:00' FROM dual UNION ALL

SELECT 'jsmith', 'Phones' , timestamp'2020-08-24 09:45:00' , timestamp'2020-08-24 09:50:00' FROM dual UNION ALL

SELECT 'bjones', 'Phones' , timestamp'2020-08-24 09:00:00' , timestamp'2020-08-24 09:10:00' FROM dual UNION ALL

SELECT 'bjones', 'Front Desk', timestamp'2020-08-24 09:05:00' , timestamp'2020-08-24 09:15:00' FROM dual UNION ALL

SELECT 'bjones', 'Phones' , timestamp'2020-08-24 09:15:00' , timestamp'2020-08-24 09:45:00' FROM dual

), t AS

(

SELECT "user" , MIN(start_time) AS min_start_time, MAX(end_time) AS max_end_time

FROM user_activities

GROUP BY "user"

), t2 AS

(

SELECT "user", min_start_time + NUMTODSINTERVAL(level, 'minute') AS minutes

FROM t

CONNECT BY level <= EXTRACT( hour FROM max_end_time - min_start_time )*60 + EXTRACT( minute FROM max_end_time - min_start_time )

AND PRIOR SYS_GUID() IS NOT NULL

AND PRIOR "user" = "user"

)

SELECT "user", COUNT(*) AS "Duration"

FROM t2

WHERE EXISTS ( SELECT *

FROM user_activities

WHERE minutes BETWEEN start_time and end_time

AND "user" = t2."user" )

GROUP BY "user"