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

with t1 as (
    select
        course_id,
        sum(if_sign) as sign_cnt
    from 
        behavior_tb
    group by 
        course_id
),

t2 as (
    select
        course_id,
        count(user_id) as attend_num
    from (
        select
            course_id,
            user_id,
            sum(continue_min) as attend_min
        from (
            select
                user_id,
                course_id,
                timestampdiff(minute, in_datetime, out_datetime) as continue_min
            from 
                attend_tb
        ) as t2_1
        group by 
            course_id,
            user_id
    ) as t2_2
    where 
        attend_min >= 10
    group by 
        course_id
)

select
    t1.course_id,
    course_tb.course_name,
    round(t2.attend_num / t1.sign_cnt * 100,2) as attend_rate
from 
    t1
left join 
    t2
on 
    t1.course_id = t2.course_id
left join 
    course_tb
on 
    t1.course_id = course_tb.course_id
order by 
    t1.course_id