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

with tb1 as (
        select
            user_id,course_id,
            case when sum(case when timestampdiff(minute, in_datetime, out_datetime) >= 10 then 1 else 0 end)>=1 then 1 else 0 end as yes
        from attend_tb a 
        group by user_id,course_id
    )
select
    c.course_id,
    c.course_name,
    round(100 * sum(yes)/ sum(if_sign),2) as attend_rate
from behavior_tb as b
left join tb1 on tb1.course_id = b.course_id and tb1.user_id = b.user_id
join course_tb as c on c.course_id = b.course_id
group by
    course_id,
    course_name
order by
    course_id asc