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

select  

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