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

# 先查找出正常用户的id
with t1 as (
    select id
    from user
    where is_blacklist = 0
)
# 找到正常用户发送给正常用户的邮件记录
select date, ROUND(sum(type='no_completed')/count(id), 3) as p
from email
where send_id in (select id from t1)
and receive_id in (select id from t1)
group by date
order by date;