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

SELECT date,round((
    select COUNT(*)
    FROM email e1,user sen,user rec
    WHERE e1.send_id = sen.id and sen.is_blacklist = 0 and e1.receive_id = rec.id and rec.is_blacklist = 0 and e1.date = email.date and e1.type = "no_completed"
)/(
    select COUNT(*)
    FROM email e1,user sen,user rec
    WHERE e1.send_id = sen.id and sen.is_blacklist = 0 and e1.receive_id = rec.id and rec.is_blacklist = 0 and e1.date = email.date
),3)
FROM email,user sen,user rec
WHERE email.send_id = sen.id and sen.is_blacklist = 0 and email.receive_id = rec.id and rec.is_blacklist = 0
GROUP BY date
ORDER BY date asc