题解 | #分别满足两个活动的人#

没有用avg,就按video_id分类后相除得到的完播率，另外用到一个左连接来找到完播的视频：

select video_id,round(sum(case when dif >= duration then 1 else 0 end)/count(uid),3) avg_comp_play_rate from (
    select uid,video_id,timestampdiff(second,start_time,end_time) dif,duration
    from tb_user_video_log 
    left join 
    tb_video_info 
    using(video_id)
    where year(start_time) = 2021
) A
group by video_id
order by 2 desc