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

-- 简单记录一下，思路非常直观：分别计算出报名人数和出勤人数表，连接之后计算出勤率
WITH t5 AS
	(SELECT * FROM
	 -- 根据if_sign计算每个课程的报名人数
	(SELECT 
		course_id,
		COUNT(DISTINCT user_id) AS sign_cnt
	FROM behavior_tb
	WHERE if_sign = 1
	GROUP BY course_id) AS t1
	LEFT JOIN
	(SELECT course_id, course_name, COUNT(DISTINCT user_id) AS attend_cnt FROM
	(SELECT 
		course_id, course_name, user_id,
	 -- 注意这里的10分钟应该同时满足用户登录和用户在直播间的时长，这里计算的是用户在直播期间在直播间停留的时间
		TIMESTAMPDIFF(MINUTE, IF(in_datetime >= begin_time, in_datetime, begin_time), IF(out_datetime >= end_time, end_time, out_datetime)) AS len
	FROM
		(SELECT 
		 -- 这里需要特别注意直播结束时间的concat情况
			course_id, course_name, CONCAT(DATE_FORMAT(course_datetime, '%Y:%m:%d %H:%i'), ':00') AS begin_time,
			CONCAT(SUBSTRING_INDEX(course_datetime, ' ',1), ' ', SUBSTRING_INDEX(course_datetime, '-', -1), ':00') AS end_time,
			in_datetime, out_datetime, user_id
		FROM attend_tb A
		RIGHT JOIN course_tb C USING(course_id)) AS t2) AS t3
	 -- 仅保留在直播间停留10分钟时间的用户
	WHERE len >= 10
	GROUP BY course_id, course_name) AS t4 USING(course_id))
SELECT 
	course_id, course_name, ROUND(attend_cnt * 100 / sign_cnt, 2) AS 'attend_rate(%)'
FROM t5
ORDER BY course_id;