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

SELECT tag, concat(ROUND(AVG(play_progress)*100, 2),'%') AS avg_play_progress
FROM(
SELECT uid, t1.video_id, tag, t2.duration, CASE WHEN timestampdiff(SECOND, start_time, end_time) >= t2.duration THEN 1 ELSE timestampdiff(SECOND, start_time, end_time)/t2.duration END AS play_progress
FROM tb_user_video_log t1
JOIN tb_video_info t2 ON t1.video_id=t2.video_id
) sub
GROUP BY tag
HAVING AVG(play_progress) > 0.6
ORDER BY avg_play_progress DESC