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

# 筛选出正常用户
select
date,
round(sum(case type when 'no_completed' then 1 else 0 end)/count(type),3) p
from email e 
left join user u1 on e.send_id = u1.id
left join user u2 on e.receive_id = u2.id
where u1.is_blacklist = 0 and u2.is_blacklist = 0
group by date
order by date

这里主要注意一下，统计某个字段特定值的方法

sum(case type when 'no_completed' then 1 else 0 end)

count(type)