题解 | 统计加班员工占比

with
    t1 as (
        select
            at.staff_id,
            timestampdiff (second, first_clockin, last_clockin) / 3600 work_time,
            department
        from
            attendent_tb at
            left join staff_tb st on at.staff_id = st.staff_id
    )
select
    department,
    concat (
        round(
            sum(
                case
                    when work_time > 9.5 then 1
                    else 0
                end
            ) / count(staff_id) * 100,
            1
        ),
        '%'
    ) ratio
from
    t1
group by
    department
order by
    ratio desc