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

with tb_sign_uv as
         (
            select course_id, sum(if_sign) sign_uv
            from behavior_tb
            group by course_id
         )
, tb_len as
    (
            select course_id, user_id
            from attend_tb
            group by course_id, user_id
            having sum(timestampdiff(second, in_datetime, out_datetime) / 60) >= 10
    )
select course_id, course_name, round(count(distinct user_id) / max(sign_uv) * 100, 2) attend_rate
from tb_len 
left join tb_sign_uv using (course_id)
left join course_tb using (course_id)
group by course_id, course_name;