先列大思路：

• 找出 receive_id和send_id 都是正常用户的id行（is_blacklist=0)
• 统计type=completed 的次数/所有次数

解法1是我自己写的第一版。整体比较冗长

select a.date,round(1-b.count/a.count,3) as p
from 
(
    select date,count(*) as count
    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
    )
    group by date
    # 所有正常的发送次数
)a
join (
    # 所有正常的用户、且type是成功的次数
    select date,count(*) as count
    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
    )
    and type='completed'
    group by date
    
)b
on a.date=b.date
order by a.date asc

解法2是参考了评论的思路，更简单一些。是我要努力的方向！

select
e.date,
round(sum(case when e.type='completed' then 0 else 1 end)*1.0 / count(e.type),3) as p
from email as e
	join user as u1 on (e.send_id=u1.id and u1.is_blacklist=0)
	join user as u2 on (e.receive_id=u2.id and u2.is_blacklist=0)
group by e.date
order by e.date asc