题解 | 统计加班员工占比

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

select * from t2