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

select 
    a1.video_id, 
    round(sum(case when a1.end_time - a1.start_time >= a2.duration then 1 else 0 end)/count(a1.video_id),3)     avg_comp_play_rate
from tb_user_video_log a1 join tb_video_info a2 on a1.video_id = a2.video_id
where year(a1.start_time) = '2021' and year(a1.end_time) = '2021' 
group by 1
order by 2 desc