select
    date,
    round(1 - avg(if (type = 'completed', 1, 0)), 3) P
from
    email e
    join user u1 on e.send_id = u1.id
    join user u2 on e.receive_id = u2.id
where
    u1.is_blacklist = 0
    and u2.is_blacklist = 0
group by
    date
order by
    date