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

select t1.video_id,
       round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(1),3) as avg_comp_play_rate
from tb_user_video_log t1
join tb_video_info t2
on t1.video_id = t2.video_id
where t1.video_id in (
                   select video_id 
                   from tb_user_video_log 
                   where year(start_time) = 2021
                    )
and year(start_time) = 2021
group by t1.video_id
order by avg_comp_play_rate desc