题解 | #各个视频的平均完播率#

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