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

select a.video_id, round(sum(a.dis)/count(a.video_id),3) res from
(select l.video_id,if(l.end_time-l.start_time>=i.duration,1,0) as dis 
 from tb_user_video_log l join tb_video_info i on l.video_id = i.video_id where year(l.start_time)=2021) a
 group by a.video_id order by res desc