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

with t as(
    select
        i.tag,
        case 
            when timestampdiff(second,p.start_time,p.end_time)*100/i.duration>=100
            then 100
            else timestampdiff(second,p.start_time,p.end_time)*100/i.duration
            end as progress
    from
        tb_video_info i inner join tb_user_video_log p using(video_id))

select
    tag,
    concat(round(avg(progress),2),'%') as avg_play_progress
from 
    t
group by
    tag
having 
    avg_play_progress>60.00
order by 
    avg_play_progress desc