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

# 1.计算各科报名人数
# 2.计算各科出勤人数
# 3.进行表联结计算出勤率

SELECT t1.course_id, ct.course_name, ROUND(attend_num * 100 / sign_num, 2) AS 
'attend_rate(%)'
FROM
(SELECT bt.course_id, COUNT(DISTINCT bt.user_id) AS sign_num
FROM behavior_tb AS bt
WHERE bt.if_sign = 1
GROUP BY bt.course_id) AS t1
LEFT OUTER JOIN 
(SELECT at.course_id, COUNT(DISTINCT at.user_id) AS attend_num
FROM attend_tb AS at
WHERE TIMESTAMPDIFF(MINUTE, at.in_datetime, at.out_datetime) >= 10
GROUP BY at.course_id) AS t2
ON t1.course_id = t2.course_id
LEFT OUTER JOIN  course_tb AS ct
ON t1.course_id = ct.course_id
ORDER BY t1.course_id;