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