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

自用

select
    tag
    ,concat(avg_play_progress,'%') avg_play_progress
from(
    select
        tag
        ,round(sum(flag) / sum(duration) * 100,2) avg_play_progress
    from(
        select
            uid
            ,t1.video_id
            ,tag
            ,duration
            ,if(timestampdiff(second,start_time,end_time) > duration,duration,timestampdiff(second,start_time,end_time) ) flag
        from
            tb_user_video_log t1
        inner join tb_video_info t2
        on t1.video_id = t2.video_id
    )t3
    group by tag
    having avg_play_progress > 60
    order by avg_play_progress desc
)t4