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

SELECT ct.course_id, ct.course_name,
    ROUND((bi2.attend_cnt/bi1.app_cnt)*100,2) `attend_rate(%)`
FROM(
    SELECT course_id, COUNT(DISTINCT user_id) app_cnt FROM behavior_tb
    WHERE if_sign = 1
    GROUP BY course_id
) bi1
JOIN (
    SELECT course_id, COUNT(DISTINCT user_id) attend_cnt FROM attend_tb 
    WHERE TIMESTAMPDIFF(minute,in_datetime,out_datetime)>=10 
    GROUP BY course_id
) bi2 USING(course_id)
JOIN course_tb ct USING(course_id)
ORDER BY course_id