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

select
video_id, round(sum(if(t.duration<=t.difftime,1,0))/count(t.uid),3) avf_comp_play_rate
from(
    select
    user.uid, user.video_id, user.start_time, user.end_time, video.author, video.duration, timestampdiff(second,user.start_time,user.end_time) difftime, video.release_time
    from
    tb_user_video_log user left join tb_video_info video
    on user.video_id = video.video_id
) t
where left(t.start_time,4) = 2021
group by video_id
order by avf_comp_play_rate desc