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

select
    t1.tag,
    concat (round(t1.avg_play_progress * 100, 2), '%') avg_play_progress
from
    (
        select
            tag,
            avg(
                if (
                    TIMESTAMPDIFF (second, start_time,end_time) >= duration,
                    duration,
                    TIMESTAMPDIFF (second, start_time,end_time)
                ) / duration
            ) avg_play_progress
        from
            tb_video_info,
            tb_user_video_log
        where
            tb_video_info.video_id = tb_user_video_log.video_id
        group by
            tag
        having avg_play_progress>0.6
        order by
            avg_play_progress desc
    ) t1