Untitled

select 
sub.*,
case when sub.dt_max_baixa is null or (sub.entregues + sub.devolvidos - 1) <= 0
then 'SEM TEMPO MEDIO'
else to_char((sub.dt_max_baixa - sub.dt_min_baixa) / (sub.entregues + sub.devolvidos - 1), 'HH24:MI:SS')
end as tempo_medio

from (

select
distinct

l.frq_id as franquia,
ll.lista_id as lista,
l.dt_hora_lib::date as dt_lib,
llh.imei as imei,
p.nome as courier,
count(llh.hawb_id) as total, 
sum(case when llh.tipo_baixa = 'ENTREGA' then 1 else 0 end) as entregues,
sum(case when llh.tipo_baixa = 'DEVOLUCAO' and llh.mot_dev_id not in (532, 601, 602) then 1 else 0 end) as devolvidos,
sum(case when llh.mot_dev_id in (532, 601, 602) then 1 else 0 end) as nao_visitado,
(CASE
  WHEN count(llh.hawb_id) = 0 THEN '0%'
  ELSE ROUND((CAST(sum(CASE WHEN llh.fora_alvo IS NOT NULL AND llh.fora_alvo > 500 and llh.fora_alvo <= 50000 THEN 1 ELSE 0 END) AS DECIMAL) / count(llh.hawb_id) * 100), 2)::text || '%'
END) as fora_alvo,
sum(CASE WHEN llh.fora_alvo IS NOT NULL AND llh.fora_alvo > 500 and llh.fora_alvo <= 50000 THEN 1 ELSE 0 END) as fora_alvo_qnt,
min(llh.dt_hora_baixa) as dt_min_baixa,
MAX(case when (llh.mot_dev_id is null or llh.mot_dev_id not in (532, 601, 602)) then llh.dt_hora_baixa else null end) AS dt_max_baixa,
sum(case when llh.dt_hora_baixa is not null then 1 else 0 end) as qnt_hawb_com_baixa

from log_listas ll
left join listas l on ll.lista_id = l.lista_id
left join log_listas_hawbs llh on l.lista_id = llh.lista_id
left join pessoas p on l.courier_id = p.pessoa_id

where
ll.frq_id in (

)
--ll.lista_id = 5332990
and ll.dt_hora_liberacao::date >= '2023-02-01' and ll.dt_hora_liberacao::date <= '2023-07-11'

group by franquia, lista, dt_hora_lib, imei, courier) as sub