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

SELECT course_id, course_name,
    ROUND(attend_cnt * 100 / sign_cnt, 2) AS 'attend_rate(%)'
FROM (
    SELECT course_id,
            COUNT(DISTINCT user_id) AS attend_cnt
    FROM attend_tb
    WHERE TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime) >= 10
    GROUP BY course_id
) a JOIN (
    SELECT course_id, course_name, 
        SUM(if_sign) AS sign_cnt
    FROM behavior_tb
        LEFT JOIN course_tb USING(course_id)
    GROUP BY course_id, course_name
) b USING(course_id)
GROUP BY course_id, course_name
ORDER BY course_id