with t1 as ( select tbv.video_id as video_id,tbv.tag as tag, sum(least(time_to_sec(timediff((time(tbu.end_time)),time((tbu.start_time)))),tbv.duration))/(count(*)*tbv.duration) as rate from tb_video_info tbv left join tb_user_video_log tbu on tbv.video_id=tbu.video_id group by tbv.video_id having rate>0.6 ) select t1.tag as tag, concat(round(avg(t1.rate)*100,2),'%') as avg_play_progress from t1 group by t1.tag order by avg_play_progress desc