题解 | 异常的邮件概率
异常的邮件概率
https://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
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
