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

select
    course_id,
    course_name,
    round(
        sum(online_time >= 10) / (
            select
                sum(if_sign)
            from
                behavior_tb
            where
                course_id = t1.course_id
        ) * 100,
        2
    ) as `attend_rate(%)`
from
    (
        select
            user_id,
            course_id,
            sum(timestampdiff (minute, in_datetime, out_datetime)) as online_time
        from
            attend_tb
        group by
            user_id,
            course_id
    ) t1
    inner join course_tb using (course_id)
group by
    course_id,
    course_name
order by
    course_id