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

# 首先 正常用户筛选，剔除掉黑名单
# send_id not in (select id from user where is_blacklist = 1)
# receive_id  not in n (select id from user where is_blacklist = 1)
# 接着，正常用户发送邮件数# 然后，正常用户发送失败邮件数
select a.date, format(b.lo_num / a.total_num,3)  as p
from (select date,count(send_id) as total_num from email 
where send_id not in (select id from user where is_blacklist = 1)
and receive_id  not in (select id from user where is_blacklist = 1)
group by date )a
join 
(select date,count(send_id) as lo_num from email 
where type = 'no_completed' 
and send_id not in (select id from user where is_blacklist = 1)
and receive_id  not in (select id from user where is_blacklist = 1)
group by date)b
on a.date = b.date
group by a.date