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

SELECT TB1.date, round(c_num/all_num,3) as prob FROM
(SELECT date, count() as c_num FROM
(SELECT send_id, receive_id, type, date, u1.is_blacklist as send_bl, u2.is_blacklist as receive_bl
FROM email JOIN user u1 ON email.send_id = u1.id
JOIN user u2 ON email.receive_id = u2.id
WHERE u1.is_blacklist = 0 and u2.is_blacklist = 0) X
WHERE type = 'no_completed'
group by date) TB1
JOIN
(SELECT date, count() as all_num FROM
(SELECT send_id, receive_id, type, date, u1.is_blacklist as send_bl, u2.is_blacklist as receive_bl
FROM email JOIN user u1 ON email.send_id = u1.id
JOIN user u2 ON email.receive_id = u2.id
WHERE u1.is_blacklist = 0 and u2.is_blacklist = 0) Y
group by date) TB2
USING (date)