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

with t1 as (
    select *
    from email
    where send_id in (
        select id
        from user
        where is_blacklist = 0
    )
    and receive_id in (
        select id
        from user
        where is_blacklist = 0
    )
)

select s1.date,
        round(ifnull((total_count - success_count) / total_count, 0), 3) as p 
from 
(select date,
        ifnull(count(id), 0) as success_count
from t1
where type = 'completed'
group by date)s1,
(select date,
        ifnull(count(id), 0) as total_count
from t1
group by date)s2
where s1.date = s2.date