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

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