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

# 先统计每个用户的在线时长
with a as(
    select
        user_id,
        course_id,
        sum(timestampdiff(second,in_datetime,out_datetime)) as zaixian
    from 
        attend_tb 
    group by 
        user_id,course_id
    having 
        zaixian/60>=10
),
b as(
    select
        course_id,
        sum(if_sign) as baoming
    from 
        behavior_tb 
    group by 
    course_id
),
c as (
    select  
        course_id,
        count(distinct user_id) as zaixiannum
    from 
        a
    group by 
        course_id
),
d as (
    select
        ct.course_id,ct.course_name,
        round(100*c.zaixiannum/b.baoming,2) as attend_rate
    from 
        course_tb as ct
        left join c on ct.course_id = c.course_id
        left join b on ct.course_id = b.course_id
    order by
        ct.course_id
)
select * from d