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

-- 条件：正常用户，is_blacklist=0
-- 分组: date
-- 聚合：总邮件数，失败邮件数
-- 表结构：日期、总邮件数、失败邮件数、失败概率

select email.date as date
      ,round(sum(case when type='no_completed' then 1 else 0 end)/count(email.id),3) as p
from email
left join user as u1
on email.send_id=u1.id
left join user as u2
on email.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by email.date
order by email.date