知识点

1. 首先需要排除所有异常用户，因此内连接两个表筛选条件为is_blacklist = 0，按照日期进行分组
2. 概率结果显示三位小数，使用round(计算概率,3)
3. 概率计算使用case when，completed是0否则为1进行奇数求和，除以计数type出现次数

代码

select date, 
round(
    sum(case e.type 
    when 'completed' then 0
    else 1 end)*1/count(e.type),3)as p
from email as e
join user as u1
on (e.send_id = u1.id
    and u1.is_blacklist = 0)
join user as u2
on (e.receive_id = u2.id
    and u2.is_blacklist = 0)
    group by e.date
    order by e.date