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

出勤率 = 在线超过10分钟的人 / 报名的人数

1. 首先找到每节课报名的人数

select course_id, 
    course_name,
    sum(if_sign) as tot_sign
    from course_tb 
    inner join behavior_tb 
    using(course_id)
    where if_sign = 1
    GROUP BY course_id,course_name

2. 接着找到每节课观看时间超过10分钟的人数

select course_id,user_id, sum(timestampdiff(minute,in_datetime,out_datetime)) as on_line
    from attend_tb 
    inner join course_tb 
    using(course_id)
    GROUP BY course_id,user_id
    having on_line >=10

3. 合并两个表单汇总求出出勤率

with t as(
select course_id, 
    course_name,
    sum(if_sign) as tot_sign
    from course_tb 
    inner join behavior_tb 
    using(course_id)
    where if_sign = 1
    GROUP BY course_id,course_name
),

t2 as(
select course_id,user_id, sum(timestampdiff(minute,in_datetime,out_datetime)) as on_line
    from attend_tb 
    inner join course_tb 
    using(course_id)
    GROUP BY course_id,user_id
    having on_line >=10
)

select course_id, course_name ,round(100*count(distinct user_id)/tot_sign,2) as attend_rate 
    from t 
    inner join t2 
    using(course_id)
    group by course_id, course_name
    order by  course_id