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

select course_id, course_name
    , round(100*sum(if_on)/count(if_on),2) as attend_rate
from (
    select course_name, course_id, user_id
        , if(sum(timestampdiff(minute,in_datetime,out_datetime))>=10,1,0) as if_on
    from course_tb
        left join behavior_tb using(course_id)
        left join attend_tb using(course_id,user_id)
    where if_sign = 1
    group by course_name, course_id, user_id
)as t1
group by course_name, course_id
order by course_id

坑点：统一用户的信息会重复出现

解决方法：group by 之后会自动去除重复