题解 | 异常的邮件概率

select dt as date,round(sum(if(re_isb = 0 and send_isb = 0 and type ='no_completed',1,0))/sum(if(re_isb = 0 and send_isb = 0,1,0)),3) as p
from 
(select C.id,C.send_id,C.receive_id,C.type,C.dt,C.send_isb,D.is_blacklist as re_isb
from 
(select A.id,A.send_id,A.receive_id,A.type,A.date as dt,B.is_blacklist as send_isb
from email A
left join user B
on A.send_id = B.id) as C
left join user D
on C.receive_id = D.id) as E
group by date
order by date