题解 | 异常的邮件概率

# 1.进行表联结
# 2.过滤黑名单用户
# 3.按日期分组统计发送失败概率
SELECT e.date, ROUND(SUM(IF(e.type = 'no_completed', 1, 0)) / COUNT(*), 3) AS p
FROM email AS e
LEFT OUTER JOIN user AS u1
ON e.send_id = u1.id 
LEFT OUTER JOIN user AS u2
ON e.receive_id = u2.id
WHERE u1.is_blacklist != 1 AND u2.is_blacklist != 1
GROUP BY e.date
ORDER BY e.date;