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

with
    t1 as(
        #每个报名人的上课情况记录
        select 
            distinct
            ct.course_id,
            ct.course_name,
            bt.user_id,
            if(in_datetime is null,0,(unix_timestamp(out_datetime)-unix_timestamp(in_datetime))/60 )`sub_minutes`
        from behavior_tb `bt`
        left join attend_tb `at`
        on bt.user_id =at.user_id and bt.course_id=at.course_id
        join course_tb `ct`
        on ct.course_id = bt.course_id
        where bt.if_sign=1
    ),
    t2 as(
        #获取每个报名人每科听课总时长的记录
        select 
            distinct
            course_id,
            course_name, 
            user_id,
            sum(sub_minutes) over(partition by course_id,course_name,user_id  ) `total_minutes`
        from t1
    )

select 
    course_id,
    course_name, 
    round(100*sum(if(total_minutes>=10,1,0))/sum(if(total_minutes is null,1,1)),2) `attend_rate(%)`
from t2
group by course_id,course_name