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

提交未成功，是因为没有限制2021年，这个条件

select 
    b.video_id 
    ,round(count(if(b.dur_time - a.duration >=0 ,1,null))/count(1),3) avg_comp_play_rate
from 
(select id
    ,uid
    ,video_id
    ,unix_timestamp(end_time)-unix_timestamp(start_time) dur_time
 from tb_user_video_log
 where year(start_time)='2021'
) b
left join 
(select video_id,duration from tb_video_info)a 
on a.video_id = b.video_id 
group by b.video_id 
order by avg_comp_play_rate desc