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

思路：首先要筛选出正常用户，就需要合并user和email表然后用where子查询；其次就是分组计算，使用group by date;难点在于概率的计算，每天发邮件的总数很好计算，关键是怎么计算发送不成功的个数，这里采用讨论区大佬的方法：sum(case t2.type when 'completed' then 0 else 1 end)
【代码】
select t.date, round(sum(case t.type when 'completed' then 0 else 1 end) / count(t.type),3) as p
from
(select e.type, e.date from
email as e left join user as u1 on e.send_id = u1.id
left join user as u2 on e.receive_id = u2.id
where u1.is_blacklist =0 and u2.is_blacklist =0
) as t
group by t.date
order by t.date
【怎么计算发送不成功的概率】
1.round(sum(type = "no_completed") / count(), 3) as p
2.round(sum(case t2.type when 'completed' then 0 else 1 end)/count(),3) as p