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

select
    t.course_id
    , t.course_name
    , round(100 * avg(case when t.attend_time >= 10 then 1 else 0 end), 2) as attend_rate
from(
    select
        c.course_id
        , c.course_name
        , b.user_id
        , sum(case when time(a.out_datetime) > '19:00:00' then timestampdiff(minute, if(time(a.in_datetime) < '19:00:00', str_to_date(concat(date(a.in_datetime), '19:00:00'), '%Y-%m-%d %H:%i:%s'), a.in_datetime), a.out_datetime) else 0 end) as attend_time
    from course_tb as c
    left join behavior_tb as b on c.course_id = b.course_id and b.if_sign = 1
    left join attend_tb as a on a.course_id = b.course_id and a.user_id = b.user_id
    group by c.course_id, c.course_name, b.user_id
    ) as t
group by t.course_id, t.course_name
order by course_id