select 
date,
round(((sum(case when type='completed' then 0 else 1 end)*1.0)/(count(type)*1.0)),3) p
from
(
select  a.*,
b.is_blacklist is1,
c.is_blacklist is2
from email a inner join user b  
on a.send_id=b.id 
inner join user c
on a.receive_id=c.id
) d
where is1=0 and is2=0
group by date
order by date