Reporting Layanan All Excel Query

select service , sub_service , sla, count(1) as jumlah_keluhan ,
        round(avg(spend_hours)) as rata_rata_jam ,
        round(((cast(count(1) filter (where not is_late) as decimal ))/cast(count(1) as decimal)*100),2) as close_sesuai_target,
        round(((cast(count(1) filter (where is_late) as decimal ))/cast(count(1) as decimal)*100),2) as close_tidak_sesuai_target
        from (select  
        
            s.name as service ,
            ss.name as sub_service,
            ss.sla,
            tsh.closed_time,
            a2.deadline,
            (tsh.closed_time::date > a2.deadline::date) as is_late,
            (EXTRACT(EPOCH FROM tsh.closed_time-t.created_at)/3600) as spend_hours
            from tickets t 
            inner join assignments a on a.ticket_id = t.id 
            inner join sub_service ss on a.subservice_id = ss.id 
            inner join services s on ss.service_id = s.id 
            left join (select max (tsh.created_at) as closed_time, tsh.ticket_id from ticket_status_history tsh 
        where tsh.type_id = 5 group by tsh.ticket_id) tsh on tsh.ticket_id = t.id
            left join (SELECT MAX
                            ( a2.end_date_assignee ) as deadline ,t2.assignment_id 
                          FROM
                            assignees a2
                          join task t2
                          on (a2.task_id = t2.id)
                          group by t2.assignment_id 
        
                         ) a2 on a2.assignment_id = a.id 
                          
            where 
                rating  = 1  and 
                a2.deadline   notnull 
                
            and a.purpose_id = 1
            and t.created_at >= '2022-01-01'
            and t.created_at <= '2023-01-01'
        
            
            group by s.id , ss.id  
            ,tsh.closed_time,a2.deadline, t.created_at) laporan 
            group by service , sub_service,sla