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

select
    tag,
    concat (
        round(
            avg(
                case
                   when timestampdiff (second, start_time,    end_time) < duration then    timestampdiff (second, start_time, end_time) / duration
                    else 1
                end
            ) * 100,
            2
        ),
        '%'
    ) avg_play_progress
from
    tb_user_video_log t,
    tb_video_info tb
where
    t.video_id = tb.video_id
group by
    tb.tag
having
    substring_index(avg_play_progress,'%',1)> 60
order by
    avg_play_progress desc