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

select tag,concat(avg_play_progress,"%") AS avg_play_progress
FROM (
select tag,
round(avg(if(TIMESTAMPDIFF(SECOND, start_time, end_time)>b.duration,1,TIMESTAMPDIFF(SECOND, start_time, end_time)/b.duration))*100,2) 
AS avg_play_progress
FROM tb_user_video_log AS a
LEFT JOIN tb_video_info AS b ON a.video_id = b.video_id
group by b.tag
having avg_play_progress > 60
order by avg_play_progress desc
) AS abc