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

select 
tag,
concat(rate1,'%') as avg_play_progress
from
(
select
tag,
round(sum(if(timestampdiff(second,start_time,end_time)>=duration,duration,timestampdiff(second,start_time,end_time)))/sum(duration)*100,2) as rate1
from tb_user_video_log tu
left join tb_video_info tv
on tu.video_id = tv.video_id
group by tag
having rate1 > 60
)t0
order by avg_play_progress desc