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

select distinct t1.video_id
,round((count(if (t1.jiange >=t1.duration,1,null)) over(partition by t1.video_id))/(count(*) over(partition by t1.video_id)),3) as cnt_all from (
select u.video_id
,TIMESTAMPDIFF(second,u.start_time,u.end_time) as jiange
,v.duration 
from tb_user_video_log as u left join tb_video_info as v 
on u.video_id=v.video_id where year(u.start_time)=2021) t1 
order by cnt_all desc