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

select f.date,round((1-sum(f.type2)/count(f.type2)),3)p
from
(select e.send_id,e.receive_id,(case when e.type='completed' then 1
                                    when e.type='no_completed' then 0
                                    end)type2,e.date,e.b1,e.b2
from 
(select c.*,d.is_blacklist b2
from
(select a.*,b.is_blacklist b1
from email a 
join user b
on a.send_id=b.id and b.is_blacklist=0) c
join
user d
on c.receive_id=d.id and d.is_blacklist=0)e)f
group by f.date order by f.date;