with t as (
    
    select workerid,min(oper_time) in_time, max(oper_time) out_time
    from(
        select round(SYS.DBMS_RANDOM.VALUE(1,100)) workerid, sysdate-SYS.DBMS_RANDOM.VALUE(0,100) oper_time,round(SYS.DBMS_RANDOM.VALUE(0,1))   oper_type 
        from all_objects 
        where rownum<=1000000
        )
    where to_number(to_char(oper_time,'HH24')) between 8 and 20
    group by workerid,trunc(oper_time)
    having (max(oper_time)-min(oper_time))*24 >1
    order by 1,2
  
)
select workerid,
    count(*) work_days,
    round(sum((out_time-in_time)*24)) work_hours,
    round(avg((out_time-in_time)*24),1) avg_work_day_hours,
    sum(case when (out_time-in_time)*24>=9 then 1 else 0 end ) good_days,
    sum(case when (out_time-in_time)*24<9 then 1 else 0 end ) not_full_days  
from t
group by workerid
order by 1;