select date, round(sum(type = "no_completed") / count(*), 3) as p
from email as t1
    join user as t2 on t1.send_id = t2.id
    join user as t3 on t1.receive_id = t3.id
where t2.is_blacklist = 0 and t3.is_blacklist = 0
group by date
order by date;