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