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

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;