select date,round(sum(case when type='no_completed' then 1 else 0 end)/count(type),3) as p
from email 
where send_id in (select id from user where is_blacklist=0) 
and receive_id in (select id from user where is_blacklist=0)
group by date
order by date

首先,找到发送和接受用户都是正常用户的情况;
其次,以日期进行分组,利用case when 语句统计每组中未成功的数量,count()统计每组中的总数量;
最后,按日期升序排列。