select
    date,
    round(sum(type = 'no_completed') / count(type),3) as p
from 
    email
where 
    send_id in (select id from user where is_blacklist = 0)
and 
    receive_id in (select id from user where is_blacklist = 0)
group by 
    date
order by 
    date