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

select date, round(sum(case when type = 'no_completed' then 1 else 0 end) / count(*), 3) p
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

这题的重点在于sum(case when type = 'no_completed' then 1 else 0 end) / count(*)， case when 和 sum 函数累加发送失败的次数。