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

select 
    video_id,    
    round(count(case when end_time-start_time >= duration then 1 else null  end)/count(video_id),3) as avg_comp_play_rate 
from (
    select 
        video_id,
        end_time,
        start_time,
        duration
    from tb_user_video_log
    right join tb_video_info using (video_id)
    where year(start_time) =2021) a 
group by video_id
order by avg_comp_play_rate desc