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

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