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

直接先筛选出不是黑名单的用户（发送和接受者）

select e.date,
  round(sum(case when e.type='no_completed' then 1 else 0 end)/count(*),3) as p
from email e
where e.send_id not in (select id from user where is_blacklist=1)
and e.receive_id not in (select id from user where is_blacklist=1)
group by e.date
order by e.date asc