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

个人比较喜欢用子函数，这样逻辑比较顺

select e.date,
case when e.type = 'completed' then 0 else 1 end c
from email e join user u on e.send_id = u.id
join user u1 on e.receive_id = u1.id
where u.is_blacklist = 0 and u1.is_blacklist = 0

第一次做的时候忘记不光发件人要是正常用户，收件人也得是正常用户，导致测试时候正确，提交就错误了 所以要做两次表连接

select a.date,  round(sum(a.c),3)/count(a.c) p
from( select e.date,
case when e.type = 'completed' then 0 else 1 end c
from email e join user u on e.send_id = u.id
join user u1 on e.receive_id = u1.id
where u.is_blacklist = 0 and u1.is_blacklist = 0 ) a
group by a.date