题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
①先查出
SELECT DATE,COUNT(TYPE) c,TYPE
FROM email m
JOIN USER ON m.send_id=user.id
WHERE m.send_id NOT IN (SELECT id FROM USER WHERE is_blacklist=1)
AND m.receive_id NOT IN (SELECT id FROM USER WHERE is_blacklist=1)
GROUP BY DATE ,TYPE
SELECT DATE,ROUND(SUM(CASE TYPE WHEN 'no_completed' THEN c ELSE 0 END)/SUM(c),3) p
FROM (
SELECT DATE,COUNT(TYPE) c,TYPE
FROM email m
JOIN USER ON m.send_id=user.id
WHERE m.send_id NOT IN (SELECT id FROM USER WHERE is_blacklist=1)
AND m.receive_id NOT IN (SELECT id FROM USER WHERE is_blacklist=1)
GROUP BY DATE ,TYPE) m1
GROUP BY DATE
