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

select date,round(sum(case when type='no_completed' then 1 else 0 end)/count(type),3) as p
from email 
where send_id in (select id from user where is_blacklist=0) 
and receive_id in (select id from user where is_blacklist=0)
group by date
order by date

首先，找到发送和接受用户都是正常用户的情况；
其次，以日期进行分组，利用case when 语句统计每组中未成功的数量，count()统计每组中的总数量；
最后，按日期升序排列。