SELECT date
,round(sum(case when type='no_completed' then 1 else 0 end)/count(*),3) 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