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

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