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

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