with no_black as (
select id from (
    select id 
    from user
    where is_blacklist=0
) t
),
send as (
select send_id,receive_id,type,date
from email e 
join no_black nb on nb.id=e.send_id
),
send_rec as (
select send_id,receive_id,type,date
from send se 
join no_black nb on nb.id=se.receive_id
)
select date,
round(sum(case when type='completed' then 0 else 1 end )/count(*)*1.0,3)
as p
from send_rec
group by date
order by date;