DV Team Efficiency - Rahil for Linda

set @startdate1 = '2020-03-01';
set @startdate2 = '2020-03-31';
use socar_malaysia;
select A.DL_created_date as DL_created_date,
Count(Distinct A.mid) as total_members,
count(distinct case when A.Timediff_in_hours <= '3'  then A.mid end) as "Less Than 3 hours",
count(distinct case when A.Timediff_in_hours > '3'  then A.mid end) as "More Than 3 hours",
ROUND((count(distinct case when A.Timediff_in_hours <= '3'  then A.mid end)/Count(Distinct A.mid))*100,2) as "less than 3 hours efficiency %",
Round((count(distinct case when A.Timediff_in_hours > '3'  then A.mid end)/Count(Distinct A.mid))*100,2) as "More than 3 hours efficiency %"
from
(select
dl.member_id as mid,
min(id) as id,
dl.state as state,
dl.created_at + interval 8 hour as dl_created_at_time,
date(dl.created_at + interval 8 hour) as DL_created_date,
(dl.updated_at + interval 8 hour) as dl_updated_hour,
ROUND(TIMESTAMPDIFF(minute,dl.created_at + interval 8 hour,dl.updated_at + interval 8 hour)/60,2) as Timediff_in_hours
from driver_licenses dl
where dl.created_at + interval '8' hour >= @startdate1
and dl.created_at + interval '8' hour <= @startdate2
and dl.state in ('approved','reject')
group by 1
order by 1 desc) A
Group by 1
order by 1 desc