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

# 重点在于计算出勤率中的出勤人数，即计算 每个科目 每个人的在线时长，再找出每个科目在线时长大于10分钟的人数

# 每个科目 每个人的在线时长 简化为 in_datetime 和 out_datetime 的差

WITH t AS (SELECT
course_id,
course_name,
user_id,
TIMESTAMPDIFF(SECOND,in_datetime,out_datetime)/60 AS online_time
FROM attend_tb
JOIN course_tb USING(course_id)),

t1 AS (SELECT
course_id,
course_name,
COUNT(DISTINCT IF(online_time>10,user_id,NULL)) AS attend
FROM t
GROUP BY course_id,course_name),

t2 AS (SELECT
course_id,
course_name,
SUM(if_sign) AS signs
FROM behavior_tb
JOIN course_tb USING(course_id)
GROUP BY course_id,course_name)

SELECT
t1.course_id,
t1.course_name,
ROUND(attend/signs*100,2) AS attend_rate
FROM t1 JOIN t2 ON t1.course_id = t2.course_id AND t1.course_name = t2.course_name;