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

SELECT tag, concat(FORMAT(avg(case when timestampdiff(second,start_time,end_time) >=duration then 100 else (timestampdiff(second,start_time,end_time)/duration*100) end),2),'%') as avg_play_progress
FROM tb_user_video_log a 
join tb_video_info b ON a.video_id=b.video_id
GROUP BY tag
HAVING avg_play_progress> '60.00%'
ORDER BY avg_play_progress desc