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

select
    tag,
    concat(round(avg(case when timestampdiff(second,start_time,end_time)/duration >=1 then 1
    else timestampdiff(second,start_time,end_time)/duration end)*100,2),'%')avg_play_progress
from
    tb_video_info s1
left join 
    tb_user_video_log s2
using(video_id)
where tag is not null
group by 1
having avg(case when timestampdiff(second,start_time,end_time)/duration >=1 then 1
    else round(timestampdiff(second,start_time,end_time)/duration,2) end)>0.6
order by 2 desc