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

select
    tag,
    concat (avg_play_progress, "%") 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
            ) avg_play_progress
        from
            tb_user_video_log uvl
            join tb_video_info vi on uvl.video_id = vi.video_id
        group by
            tag
        having
            avg_play_progress > 60
        order by
            avg_play_progress desc
    ) t_progress