select `date`, round(avg(if(type='no_completed',1,0)),3) p
from(
select send_id, receive_id, type, `date`
from email e left join `user` u1
on e.send_id = u1.id
left join `user` u2
on e.receive_id = u2.id
where u1.is_blacklist <> 1 and u2.is_blacklist <> 1) t1     # 先从Email表中剔除所有设计黑名单用户的记录
group by `date`
order by `date`