题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
思路：
- 因为必须要是正常用户，所以需要判断发送者和接收者必须都为正常用户，即 is_blacklist = 0，以此来排除黑名单用户
- 再根据日期分组，求出失败的概率：发送不成功的次数/count(日期)
- 最后根据日期升序排序
完整代码：
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;
SQL练习 文章被收录于专栏
已完成牛客的SQL练习。接下来是算法的练习
