题解 | 异常的邮件概率

with t3 as(select
t1.email_id,
t1.send_id,t1.ifsendblack,
t2.receive_id,t2.ifreceiveblack,
t2.type,t2.date
from
(select
e.id as email_id,
e.send_id,u.is_blacklist as ifsendblack
from email e
left join user u
on e.send_id=u.id) t1
left join
(select
e.id as email_id,
e.receive_id,e.type,e.date,u.is_blacklist as ifreceiveblack
from email e
left join user u
on e.receive_id=u.id) t2
on t1.email_id=t2.email_id)

select
t5.date as date,
round(t5.cnt1*1.0/t6.cnt2,3) as is_blacklist
from
(select
t3.date,count(*) as cnt1
from t3
where t3.ifsendblack=0 and t3.ifreceiveblack=0 and t3.type='no_completed'
group by t3.date)t5
left join
(select
t3.date,count(*) as cnt2
from t3
where t3.ifsendblack=0 and t3.ifreceiveblack=0
group by t3.date)t6
on t5.date=t6.date