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

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

注意：抠细节