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

理解业务含义，播放完成度最大是100%，当播放时间大于实际时长时，按播放时间算。

select t3.tag,
concat(round(avg(case when t3.r > 1 then 1 else t3.r end) * 100, 2), '%')
from (
select t1.video_id, 
timestampdiff(second, t1.start_time, t1.end_time) / t2.duration as r,
    t2.tag
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
) t3
group by 1 
having avg(t3.r) > 0.6
order by 2 desc