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

select
    tag,
    concat(round(round(avg(if(date_diff / duration>1,1,date_diff/duration)),4)*100,2),"%") avg_rate
    # concat(round(avg(if(date_diff / duration>1,1,date_diff/duration)),4)*100,"%") avg_rate
from
    (
        select
            g.video_id,
            timestampdiff (second, start_time, end_time) date_diff,
            tag,
            duration
        from
            tb_user_video_log g
            left join tb_video_info i on g.video_id = i.video_id
    ) a group by video_id
    having round(avg(date_diff / duration),2)*100>60
    order by avg_rate desc