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

SELECT tag,
CONCAT(CAST(
    round(100*AVG(IF(TIMESTAMPDIFF(second, start_time, end_time)<=duration,TIMESTAMPDIFF(second, start_time, end_time),duration)/duration),2)
AS CHAR),'%') avg_play_progress
FROM tb_user_video_log JOIN tb_video_info ON tb_user_video_log.video_id=tb_video_info.video_id
GROUP BY tag
HAVING AVG(IF(TIMESTAMPDIFF(second, start_time, end_time)<=duration,TIMESTAMPDIFF(second, start_time, end_time),duration)/duration)>0.6
ORDER BY avg_play_progress DESC