sql script

select e.date, round(sum(case when e.type='no_completed' then 1 else 0 end)/count(e.type),3)
from email e
where e.send_id in (select id from `user` where is_blacklist=0)
and e.receive_id in (select id from `user` where is_blacklist=0)
group by date
order by date