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

with t1 as(
    select video_id,
    timestampdiff(second,start_time,end_time)-duration as dif
    from tb_user_video_log left join tb_video_info using(video_id)
    where year(start_time)=2021
)
select video_id,
round(count(if(dif>=0,true,null))/count(*),3) as avg_comp_play_rate
from t1
group by video_id
order by avg_comp_play_rate desc