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

select e.date,round(c1.cnt1/c2.cnt2,3)
from email e join
(select date,count(type)as cnt1
from email
where type='no_completed' and
send_id in (select id
            from user
            where is_blacklist=0) and
receive_id in (select id
            from user
            where is_blacklist=0)
group by date)as c1 on e.date=c1.date
join
(select date,count(*)as cnt2
from email
where send_id in (select id
            from user
            where is_blacklist=0) and 
 receive_id in (select id
            from user
            where is_blacklist=0)
group by date)as c2 on e.date=c2.date
group by date