题解 | #平均播放进度大于60%的视频类别#

SELECT t1.tag,
	CONCAT(FORMAT(AVG(t1.play_progress)*100 , 2), '%')  AS avg_play_progress
FROM
(SELECT 
	tag,
	IF((TIMESTAMPDIFF(SECOND, start_time, end_time)) / duration > 1, 1, (TIMESTAMPDIFF(SECOND, start_time, end_time)) / duration) AS play_progress 
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)) AS t1
GROUP BY t1.tag
-- 这里要注意不能直接写avg_play_progress,因为avg_play_progress这是不再是一个可以比较的数值了
HAVING AVG(t1.play_progress) > 0.6
ORDER BY avg_play_progress DESC;