题解 | #异常的邮件概率#
异常的邮件概率
https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
SELECT f1.date, CONVERT(IFNULL(f2.fail_num / COUNT(1),0.000), DECIMAL(4,3)) AS P FROM email AS f1 LEFT JOIN (SELECT date, COUNT(1) AS fail_num 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) AND type = 'no_completed' GROUP BY date) AS f2 ON f1.date = f2.date WHERE f1.send_id IN (SELECT id FROM user WHERE is_blacklist = 0) AND f1.receive_id IN (SELECT id FROM user WHERE is_blacklist = 0) GROUP BY f1.date;