题解 | #邮件异常概率#

select date,
round(count(if(type='no_completed',id,null))/count(email.id),3) as p
 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)
  group by date
  order by date
  ;

这个不仅仅是计算出发件人不是黑名单的情况，还有一种是收件人也不是黑名单的情况，要做到发件人和收件人均不是黑名单才是正常用户对正常用户；