题解 | #异常的邮件概率#

先筛选出所有正常用户发送到正常用户的邮件信息，再统计失败的比例

with deled_email as(
select 
    *,
    (case when type='no_completed' then 1 else 0 end) as yes
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,
    FORMAT(sum(yes) / count(id),3)
from deled_email
group by date
order by date asc

也可以直接在group by前进行筛选，不用建新表