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