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

select
    tag,
    concat(round(avg(halfrate)*100, 2), '%') as avg_play_progress
from
    (
        select
            tag,
            if (
                timestampdiff (second, start_time, end_time) >= duration,
                1,
                timestampdiff (second, start_time, end_time)/duration
            ) as halfrate
        from
            tb_user_video_log
            join tb_video_info using (video_id)
    ) t1
group by
    tag
having
    avg(halfrate) > 0.6
order by
    avg_play_progress desc

难度不大，就是要知道在实例中 有一个旅游的观看占比只有60%需要去除