题解 | 异常的邮件概率
异常的邮件概率
https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
select a.date, round(sum(a.cnt)/count(a.cnt),3) p from (select date, case type when 'no_completed' then 1 else 0 end cnt 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)) a group by a.date order by a.date
一、(select id from user where is_blacklist=1) 其中这一步是选取出黑名单用户id，以确保发送人和收件人不在此名单里。
二、(select date, case type when 'no_completed' then 1 else 0 end cnt 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)) 给未发送成功和发送成功分别定义“1”，"0"
