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

select
    b.tag,
    concat(round(avg(case when UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time) <= b.duration
                then (UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time)) / b.duration
                else 1 end)*100,2),'%') avg_play_progress
from tb_user_video_log a
inner join tb_video_info b on a.video_id = b.video_id
group by b.tag
having avg_play_progress > '60.00%'
order by avg_play_progress desc;