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

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