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

思路：

1. 因为必须要是正常用户，所以需要判断发送者和接收者必须都为正常用户，即 is_blacklist = 0，以此来排除黑名单用户
2. 再根据日期分组，求出失败的概率：发送不成功的次数/count(日期)
3. 最后根据日期升序排序

完整代码：

SELECT date, ROUND(SUM(type = 'no_completed') / COUNT(*), 3) AS p
FROM email 
JOIN user AS u1
ON email.send_id = u1.id AND u1.is_blacklist = 0
JOIN user AS u2
ON email.receive_id = u2.id AND u2.is_blacklist = 0
GROUP BY date
ORDER BY date;