select 
date,
round(sum(if(e1.type = "no_completed",1,0)) / 
count(e1.type),3)
 from email e1
left join user b1
on e1.send_id = b1.id
left join user b2
on e1.receive_id = b2.id
where b1.is_blacklist = 0 
and b2.is_blacklist = 0
group by date
order by date;