①先用timestampdiff算出视频播放时间，再与t_v_i表进行左连接（左连接是因为题目要的是有播放记录的数据，故把播放记录表放在左侧即可，其他没有播放记录的视频就不会连接进来）；
②根据视频播放时间是否>=duration来判断、计算完播率。

select video_id, 
round(count(if(finish_video>=duration,1,null))/count(video_id),3) avg_comp_play_rate
from
(select video_id, start_time, timestampdiff(second,start_time,end_time) as finish_video
from tb_user_video_log) as tb1
left join tb_video_info using(video_id)
where year(start_time)=2021
group by video_id
order by avg_comp_play_rate desc