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

select tag, concat(round(avg(ratio)*100,2),'%') avg_play_progress
from (
select tag, a1.video_id, uid, case when timestampdiff(second,start_time,end_time)/a2.duration < 1 then 
timestampdiff(second,start_time,end_time)/a2.duration else 1 end ratio
from tb_user_video_log a1 join tb_video_info a2 on a1.video_id = a2.video_id
) a
group by 1
having avg(ratio) > 0.6
order by 2 desc