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

# 4/17 10:43 ~ 
# 字段：course_id、course_name、attend_rate(%)
# 出勤率 = 出勤人数(在线时长10分钟以上) / 报名人数

# tb1：出勤人数, 按照user_id、course_id分组，统计出勤时间
with tb1 as (
    select user_id,course_id,course_name,sum(timestampdiff(second,in_datetime,out_datetime)) as on_time,if_sign
    from attend_tb left join course_tb using(course_id)
    left join behavior_tb using(user_id,course_id)
    # 统计课程开始后进入的用户
    # where course_datetime between in_datetime and out_datetime or course_datetime < out_datetime
    group by user_id,course_id,course_name,if_sign
),
# tb2：出勤人数
tb2 as(
    select course_id,course_name,count(user_id) as attend_num
    from tb1 
    where on_time > 600
    group by course_id,course_name
),
# tb3：报名人数
tb3 as(
    select course_id, count(if_sign) as sign_num
    from  behavior_tb
    where if_sign = 1
    group by course_id
)
select course_id,course_name,round(100*attend_num/sign_num,2) as attend_rate
from tb2 left join tb3 using(course_id)
# 注意点：报名人数和出勤人数的表不能用同一原始表