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

-- 保留小数这个块有点无语  搞不明白
select tag,concat(round(round(avg(lt),4)*100,2),'%') as avgp
from (
select 
tag,
case when look_time/duration>1 then 1
else look_time/duration end as lt
from (
    select
video_id,TIMESTAMPDIFF(SECOND,start_time,end_time) as look_time
from tb_user_video_log
) t left join tb_video_info using(video_id)
) tt group by 1 having AVG(lt) > 0.6 order by 2 desc