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

with t1 as(
    select course_id, course_name, sum(if_sign) as sign_ct
    from course_tb right join behavior_tb using(course_id)
    group by course_id, course_name
),
t2 as(
    select course_id,
    count(1) as attend_ct
    from
    (select distinct user_id,course_id
    from attend_tb
    where timestampdiff(minute,in_datetime,out_datetime)>=10) t3
    group by course_id
)
select course_id, course_name,
round(100*attend_ct/sign_ct,2) as attend_rate
from t1 join t2 using(course_id)
order by course_id asc