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

#主要的坑点在于：有人提前进入了直播间，而提前的时间并计算到在线时长中
SELECT course_id
      ,course_name
      ,ROUND(100*SUM(is_online)/COUNT(course_id),2) AS attend_rate
FROM
(
SELECT t1.course_id
      ,user_id
      ,in_datetime
      ,out_datetime
      ,b_cid
      ,rk
      ,course_name
      ,IF(TIMESTAMPDIFF(MINUTE,in_datetime,out_datetime)>=10,1,0) AS is_online
FROM
(
SELECT a.user_id
      ,a.course_id
      #,course_name
      ,IF(LEFT(course_datetime,15)>=in_datetime,LEFT(course_datetime,15),in_datetime) AS in_datetime
      ,out_datetime
      ,b.course_id AS b_cid
      ,ROW_NUMBER() OVER(PARTITION BY a.user_id,course_name) AS rk
FROM behavior_tb AS a LEFT JOIN attend_tb AS b
ON a.user_id = b.user_id AND a.course_id = b.course_id
 LEFT JOIN course_tb AS c
ON b.course_id = c.course_id
WHERE if_sign = 1
) AS t1 LEFT JOIN course_tb ON t1.course_id = course_tb.course_id #关联course_tb是因为发生了课程名流失的问题
WHERE rk=1
) AS t2
GROUP BY course_id,course_name
ORDER BY course_id