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

with sign_table as(
    select course_id,course_name,sum(if_sign) as sign_num
    from behavior_tb left join course_tb using(course_id)
    group by course_id,course_name
)


select
course_id,course_name,
round(100 * count(course_id) / sign_num ,2) as attend_rate
from
(
select course_id,user_id,sum(watch_time) as sum_watch_time
from
(
select course_id,user_id,
timestampdiff(MINUTE,in_datetime,out_datetime) as watch_time
from attend_tb
order by course_id,user_id ) t1
group by course_id,user_id
having sum_watch_time >= 10 ) t2 left join sign_table using(course_id)
group by course_id,course_name
order by course_id

建立一个临时表查询报名人数

再计算记录中每次的观看时间，按课程和用户分组后累加，选取出观看分数大于等于10的数据

再嵌套查询，计算每个课程中大于10分钟的观众个数，再连接报名表，计算出勤率