题解 | 异常的邮件概率

-- 首先要找出正常用户 再去链接 之后按日期分组计算概率
-- 这里特别注意，要保证接收者和发送者都是白名单用户
SELECT 
    t2.date,
    ROUND(SUM(IF(t2.type = 'no_completed',1,0)) / COUNT(*),3) P
FROM user t1
INNER JOIN email t2
ON t1.id = t2.send_id
WHERE t1.is_blacklist = 0
AND t2.receive_id IN(
    SELECT receive_id
    FROM email t1
    INNER JOIN user t2
    ON t1.receive_id = t2.id
    WHERE t2.is_blacklist = 0
)
GROUP BY t2.date
ORDER BY t2.date