题解 | #各个视频的平均完播率#
平均播放进度大于60%的视频类别
http://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef
concat(
round(avg(case when timestampdiff(second,u.start_time,u.end_time)>=v.duration then 1 else
timestampdiff(second,u.start_time,u.end_time)/v.duration end)*100,2),'%')
as avg_play_progress
from tb_video_info v join tb_user_video_log u
on v.video_id = u.video_id
group by v.tag
having avg_play_progress>60
order by avg_play_progress desc