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

select
tag
,concat(round((td/st)*100,2),'%') avg_play_progress
from
(
    select
    tag
    ,sum(case when timestampdiff(second,start_time,end_time) >= duration then 1 
        else   timestampdiff(second,start_time,end_time)/duration end) td
    ,count(start_time) st
    from tb_user_video_log tu left join tb_video_info tv
    on tu.video_id = tv.video_id
    group by 1
)table1
where td/st > 0.6 #筛选条件
order by 2 desc; #注意顺序