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

select 
    t2.course_id,
    t2.course_name,
    round(100*cnt/total,2)
from(
select 
    course_id,
    course_name,
    count(distinct if(tm>=10,user_id,null)) as cnt
from(
        select
            user_id,
            course_id,
            course_name,
            sum(timestampdiff(minute,if(course_datetime > in_datetime,course_datetime,in_datetime),out_datetime)) as tm
        from 
            attend_tb as a
    left join course_tb as b
    using(course_id)
    where 
        user_id in (
            select 
                user_id
            from 
                behavior_tb
            where 
                if_sign = 1
        )
    group by 1,2,3) as t
group by 1,2) as t1
right join(
select 
    course_id,
    course_name,
    sum(if_sign) as total
from 
    behavior_tb
left join course_tb 
using(course_id)
group by 1,2) as t2
on t1.course_id = t2.course_id
order by course_id asc