with temp as
(select e.date, case type when 'completed' then 0 else 1 end as type
from email e left join user u1 on e.send_id=u1.id
left join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0)

select date, round(avg(type), 3) as p
from temp
group by date
order by date