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

select q1.date date, round(count1/count,3) p
from
(select a.date date, count(a.id) count
from (select *
from email
where send_id not in (
    select id
    from user
    where is_blacklist = 1
)
and receive_id not in (
    select id
    from user
    where is_blacklist = 1
)) a ##查找只有正常用户发送给正常用户的邮件信息
group by a.date) q1 ##按日期分组查找对应日期的邮件总数

left join 

(select a.date date, case when count(a.id) is null then 0# case count(a.id) when null then 0
                     else count(a.id)
                     end as count1
from (select *
from email
where send_id not in (
    select id
    from user
    where is_blacklist = 1
)
and receive_id not in (
    select id
    from user
    where is_blacklist = 1
)) a ##复制粘贴上方的a表，查找只有正常用户发送给正常用户的邮件信息
where a.type = 'no_completed'
group by a.date) q2 ##按日期分组查找对应日期的失败的邮件总数

on q1.date = q2.date;