题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select t1.video_id,round(sum(if(TIMESTAMPDIFF(SECOND,t1.start_time,t1.end_time)>=t2.duration,1,0))/count(t1.start_time),3) avg_comp_play_rate from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id=t2.video_id where year(t1.start_time)=2021 group by t1.video_id order by avg_comp_play_rate desc
