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

-- 思路：
--		1.attend_tb表 先分组聚合统计出每个学员每门课上课的总时长，并筛选出上课时长>=10分钟的学员信息
--		2.behavior_tb表关联course_tb表统计出每个课程报名的人数
--		3.1与2结果表inner join 分组统计每门课程的出勤率[注意格式化]
select
    t1.course_id,
    t2.course_name,
    round((count(t1.user_id) / t2.sign_cnt)*100, 2) as 'attend_rate(%)'
from(
select
    user_id,
    course_id,
    sum(timestampdiff(MINUTE,in_datetime, out_datetime)) as attend_total_time
from attend_tb
group by user_id,course_id
having sum(timestampdiff(MINUTE, in_datetime, out_datetime)) >= 10
) t1 inner join (
    select
        c.course_name,
        b.course_id,
        count(distinct b.user_id) as sign_cnt
    from behavior_tb b left join course_tb c on b.course_id = c.course_id
    where b.if_sign = 1
    group by c.course_name,b.course_id
) t2  on t1.course_id = t2.course_id
group by t1.course_id,t2.course_name
order by t1.course_id asc