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

select t1.video_id, 
round(sum(if(timestampdiff(second,start_time,end_time)-duration>=0,1,0))/count(*) ,3) avg_comp_paly_rate
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where start_time between '2021-01-01' and '2021-12-31'
and end_time between '2021-01-01' and '2021-12-31'
group by 1
order by 2 desc