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

with t1 as (
select video_id,tag,
 timestampdiff(second,start_time,end_time)/duration as play_rate
from tb_user_video_log join tb_video_info using(video_id)    

    )

select tag,
concat(
    round(
        avg(
            case when play_rate >=1 then 1 else play_rate end )*100,2),'%') 
        as avg_play_progress
from t1
-- where play_rate > 0.6 注意是类别的0.6 不是单个视频的0.6
group by tag
having avg( case when play_rate >=1 then 1 else play_rate end ) > 0.6
order by avg_play_progress desc

这个题需要格式准备，导致代码有点臃肿。