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

select
    date,
    p
from
    (
        select
            id,
            send_id,
            round(
                (
                    sum(if (type = 'completed', 0, 1)) over (
                        partition by
                            date
                        order by
                            date
                    )
                ) / (
                    count(type) over (
                        partition by
                            date
                        order by
                            date
                    )
                ),
                3
            ) as p,
            date
        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
            ) 
    ) a
group by
    date,p