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

select 
    t.tag as tag 
    ,concat(t.s,'%') as avg_play_progress
from 
(
select 
    b.tag tag
    ,round(
        avg(
            if(
                (timestampdiff(second,a.start_time,a.end_time)>b.duration),1,
            timestampdiff(second,a.start_time,a.end_time)/b.duration) 
                )*100
            ,2)         
            as s
from 
    tb_user_video_log a
left join 
    tb_video_info b 
on 
    a.video_id=b.video_id 
group by 
    b.tag 
order by 
    s desc
) t 
where t.s > 60
order by 
    avg_play_progress desc