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

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