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

select video_id,round(avg(rate),3) as avg_comp_play_rate from
(select video_id,if(times>=duration,1,0) as rate from
    (select l.id,l.uid,l.video_id,(l.end_time-l.start_time) as times,
        i.duration from tb_user_video_log l join tb_video_info i 
            on l.video_id = i.video_id where left(l.start_time,4) = '2021') t) p group by video_id order by avg_comp_play_rate desc;

一道题写了15分钟....我才是废物