select

s.department,

concat(

round(

sum(if(a.attend_date > 9.5,1,0)) /

count(1) * 100

,1)

,'%') ratio

from

staff_tb s

join (

select staff_id,

round(

timestampdiff (minute, first_clockin, last_clockin) / 60,

1

) attend_date

from

attendent_tb

) as a on s.staff_id = a.staff_id

group by s.department

order by ratio desc, s.department