存在同个用户多次进入直播的情况 | #牛客直播各科目出勤率#

SELECT
    course_id,
    course_name,
    ROUND(100 * COUNT(DISTINCT CASE WHEN timestampdiff(second, in_datetime, out_datetime) >= 10*60 THEN a.user_id END) / COUNT(DISTINCT CASE WHEN if_sign = 1 THEN b.user_id END), 2) AS attend_rate
FROM
    course_tb
JOIN
    behavior_tb b USING(course_id)
JOIN
    attend_tb a USING(course_id)
GROUP BY
    course_id, course_name
ORDER BY
    course_id