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

SELECT 
	c.course_id,
    c.course_name,
    ROUND(SUM(IF(online_time >= 10 AND if_sign = 1, 1, 0)) / SUM(if_sign) * 100, 2) AS 'attend_rate(%)'
FROM behavior_tb b
LEFT JOIN course_tb c
ON b.course_id = c.course_id
LEFT JOIN(
SELECT
	user_id,
    course_id,
    SUM(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)) AS online_time
FROM attend_tb
GROUP BY user_id, course_id
) AS tb1
ON b.user_id = tb1.user_id AND b.course_id = tb1.course_id
GROUP BY c.course_id, c.course_name
ORDER BY c.course_id ASC