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

select date ,round(sum(
case type when 'completed' then 0 else 1 end
             )/count(date),3)                                    -- 如果成功就是0，不成功就是1
from email  e left join user u
on e.id=u.id
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;