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