题解 | #异常的邮件概率#

①先查出
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