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

#sum(type = "no_completed") / count(*) 等价于 sum(case when type = "no_completed" then 1 else 0 end) / count(*)

select date, round(sum(type = "no_completed") / count(*), 3) as p
from email as t1
     join user as t2 on t1.send_id = t2.id
     join user as t3 on t1.receive_id = t3.id
where t2.is_blacklist = 0 and t3.is_blacklist = 0
group by date
order by date;