逻辑相对清晰

# 创建临时表，筛出不符合条件的对象
with t as (
    select *
    from email
    where send_id not in(select id from user where is_blacklist=1 ) 
      and receive_id not in (select id from user where is_blacklist=1)

)

# 根据日期做分组，算就完了
select date,round(sum(type='no_completed')/count(1),3) as p

from t
group by date