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

select tuvl.video_id,round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(1),3) avg_comp_play_rate
from tb_user_video_log tuvl
join tb_video_info tvi
on tuvl.video_id = tvi.video_id
where left(start_time,4) = '2021'
group by tuvl.video_id
order by avg_comp_play_rate desc