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

select 
    s1.video_id,
    coalesce(round(count(s2.video_id)/nullif(count(s1.video_id),0),3),0) avg_comp_play_rate

from
    tb_user_video_log s1
left join
    tb_video_info s2
on
    s1.video_id = s2.video_id
and 
    timestampdiff(second,start_time,end_time) >= duration
where (year(start_time),year(end_time)) = (2021,2021)
group by 1
order by 2 desc