题解 | 统计加班员工占比
WITH t1 AS(
SELECT
staff_id,
IF(timestampdiff(MINUTE,first_clockin,last_clockin)/60>9.5,'overtime','usual') as status
FROM attendent_tb
)
SELECT
department,
concat(round(COUNT(CASE WHEN status = 'overtime' THEN 1 END)/COUNT(status)*100,1),'%') as ratio
FROM staff_tb JOIN t1 USING(staff_id)
GROUP BY department
ORDER BY ratio DESC
