题解 | 异常的邮件概率

select
    date,
    round(
        (
            sum(
                case
                    when type = 'no_completed' then 1
                    else 0
                end
            ) / count(type)
        ),
        3
    ) p
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
    date
order by
    date asc

,先得到一张没有黑名单用户的表，再求概率