题解 | #牛客直播各科目出勤率#

WITH t1 AS 
(
SELECT 
user_id,
course_id,
SUM(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)) AS time

FROM attend_tb

GROUP BY user_id, course_id
)

SELECT
course_id,
course_name,
ROUND(COUNT(time >= '10' OR NULL)/SUM(if_sign)* 100, 2) AS 'attend_rate(%)'

FROM behavior_tb
LEFT JOIN course_tb
    USING(course_id)
LEFT JOIN t1
    USING(user_id, course_id)

GROUP BY course_id, course_name

ORDER BY course_id