题解 | 异常的邮件概率

with
t1 as(
    select
        send_id,
        u1.is_blacklist as u1b,
        receive_id,
        u2.is_blacklist as u2b,
        date,
        type
    from
        email as e
        left join user as u1 on e.send_id=u1.id
        left join user as u2 on e.receive_id=u2.id
)

select
    date,
    round(count(
        case
            when type='no_completed' then 1
            else null
        end
    )/count(type),3) as p
from
    t1
where
    u1b !=1 and u2b !=1
group by
    date