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

select 
    tag
    ,concat(avg_play_progress,'%')
from (
    select 
        tag
        ,round(sum(if(whatch_time>duration,duration,whatch_time))/sum(duration)*100,2) as avg_play_progress
    from(
        select 
            video_id
            ,TIMESTAMPDIFF(SECOND,start_time,end_time) as whatch_time
        from tb_user_video_log
    ) as t1
    left join (
        select 
            video_id
            ,tag
            ,duration
        from tb_video_info
    ) as t2 on t1.video_id = t2.video_id
    group by tag
) t
where avg_play_progress > 60
order by avg_play_progress desc;