题解 | #异常的邮件概率#

写个不连表的，我的习惯一般首先考虑子查询再考虑连表，所以这个的第一思路就是：想要正常的用户，那我构造一个都是正常用户的表不就好了，通常构造新表也是常用思路之一

select

    date,

    round(sum(case when type='no_completed'then 1 else 0 end)/count(1),3) p

from

    (select type,date

    from email

    where send_id not in (select id from user where is_blacklist=1)

    and receive_id not in (select id from user where is_blacklist=1))t1

group by date

order by date asc