题解 | #牛客直播各科目出勤率#
牛客直播各科目出勤率
https://www.nowcoder.com/practice/0cab547df4f0430b93042128f445d899
明确题意:
请你统计每个科目的出勤率(attend_rate(%)),出勤率=出勤(在线时长10分钟及以上)人数 / 报名人数
问题分解:
关联上课情况表和用户行为表:left join attend_tb at on bt.user_id = at.user_id and bt.course_id = at.course_id
按用户,科目分组:group by bt.user_id,bt.course_id,if_sign
计算用户每个科目在线时长:sum(timestampdiff(minute, in_datetime, out_datetime))
关联课程表和用户报名与在线时长情况表:join course_tb ct on t1.course_id = ct.course_id
按科目分组:group by ct.course_id, course_name
计算出勤率:sum(if(duration >= 10, 1, 0)) / sum(if_sign) * 100
细节问题:
结果保留两位小数:round(x, 2)
按course_id升序排序:order by ct.course_id
select ct.course_id, course_name, round(sum(if(duration >= 10, 1, 0)) / sum(if_sign) * 100, 2) 'attend_rate(%)' from( select bt.user_id, bt.course_id, if_sign, sum(timestampdiff(minute, in_datetime, out_datetime)) duration from behavior_tb bt left join attend_tb at on bt.user_id = at.user_id and bt.course_id = at.course_id group by bt.user_id,bt.course_id,if_sign ) t1 join course_tb ct on t1.course_id = ct.course_id group by ct.course_id, course_name order by ct.course_id;