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

select tag, concat(round(avg(play_progress) * 100, 2), '%') as avg_play_progress
from (
    select tag, video_id, if(td > duration, 1, td / duration) as play_progress
    from (
        select t1.video_id, tag, duration, timestampdiff(second, start_time, end_time) as td
        from tb_video_info t1 
        join tb_user_video_log t2 using(video_id)
        ) a
) b
group by tag
having avg_play_progress > 60
order by avg_play_progress desc