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

with inter as (select id from user where is_blacklist = 0)
select date
     , round(sum(n) / count(n), 3) as p
from (select date
           , if(type = 'no_completed', 1, 0) as n
      from email
      where send_id in (select id from inter)
        and receive_id in (select id from inter)) f
group by date
order by date