select
date,
round(sum(if(type='completed',0,1)) / count(*), 3)as p
from (
select *
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
)
) t1
group by date
order by date;