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

正常用户发送给正常用户邮件失败的概率（思路）:
首先获取到所有正常的email id（send_id和receive_id均为正常用户）
然后再在这些email id中按date进行聚合，计算type为no_completed的email id数量除以所有email id的数量

select date,
       round(count(distinct if(type='no_completed',id,null))/count(distinct id),3) as p
from email
where id in( -- 所有正常的email id
    select t1.id
    from (
        select t1.id
          from email t1
          join user t2
            on t1.send_id=t2.id
         where t2.is_blacklist=0
         ) t1 -- 发送用户为正常用户的emial id
    join (
        select t1.id
          from email t1
          join user t2
            on t1.receive_id=t2.id
         where t2.is_blacklist=0
         ) t2 -- 接收用户为正常用户的email id
      on t1.id=t2.id
)
group by date
order by date