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

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