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

with k as(select a.video_id video_id,timestampdiff(second,start_time,end_time) c,duration,(timestampdiff(second,start_time,end_time))-duration d from tb_user_video_log a join tb_video_info b on a.video_id=b.video_id where date_format(start_time,'%Y')=2021
and date_format(end_time,'%Y')=2021)

select video_id,round(sum(if(d>=0,1,0))/count(video_id),3) avg_comp_play_rate from k group by video_id order by avg_comp_play_rate desc