题解 | 异常的邮件概率

select f.date, round(c.comp / f.full, 3)
from (
    select a.date, count(*) as full
    from(
        select
        e.*,
        u1.is_blacklist as send_black, 
        u2.is_blacklist as receive_black
        from email e
        join user u1 on e.send_id = u1.id 
        join user u2 on e.receive_id = u2.id
        where u1.is_blacklist = u2.is_blacklist        
        ) a
    group by a.date
) f
join (
    select a.date, count(*) as comp
    from(
        select
            e.*,
            u1.is_blacklist as send_black, 
            u2.is_blacklist as receive_black
        from email e
        join user u1 on e.send_id = u1.id 
        join user u2 on e.receive_id = u2.id
        where u1.is_blacklist = u2.is_blacklist
        and e.type = 'no_completed'
        ) a
    group by a.date
) c 
on f.date = c.date
order by f.date
;