题解 | 异常的邮件概率

SELECT
    date,
    round(sum(CASE WHEN type = 'no_completed' THEN 1 ELSE 0 END)/count(type),3)
FROM
    email e JOIN user u ON e.send_id = u.id 
    JOIN (SELECT * FROM user u) u2 ON e.receive_id = u2.id
WHERE 
    u.is_blacklist = 0 AND u2.is_blacklist = 0
GROUP BY   
    date
ORDER BY date