select date,(round((count(type='no_completed' or null))/count(1),3)) p from (select date,type from email e 
where exists (select * from user u where e.send_id=u.id and  is_blacklist=0 ) 
 and exists(select * from user u where e.receive_id=u.id and  is_blacklist=0 )
 )r1
 group by date order by date

真是有趣