题解 | #各个视频的平均完播率#

SELECT 
    t3.video_id,
    ROUND(t3.ranking / t3.count, 3) AS avg_comp_play_rate
FROM
-- 第三章子表，填充NULL为0
	(SELECT 
		video_id,
		MAX(count) AS count,
		CASE WHEN MAX(ranking) IS NULL THEN 0
		ELSE MAX(ranking)
		END AS ranking
	FROM
	 -- 第二章子表，计算播放视频总数量
	(SELECT 
	video_id,
	COUNT(id) AS count
	FROM tb_user_video_log
    WHERE YEAR(start_time) = 2021
	GROUP BY video_id) AS t1
	LEFT JOIN
	 -- 第一张子表，计算完播视频数量
	  (SELECT u1.video_id, 
	  ROW_NUMBER() OVER(PARTITION BY u1.video_id) AS ranking
	  FROM tb_user_video_log u1
	  LEFT JOIN tb_video_info v1 USING(video_id) 
	  WHERE DATE_ADD(u1.start_time , INTERVAL v1.duration SECOND) <= u1.end_time AND
		YEAR(start_time) = 2021) AS t2 USING(video_id)
	GROUP BY video_id) AS t3
    ORDER BY ROUND(t3.ranking / t3.count, 3) DESC;