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

select distinct 
    date,
    round(ration, 3)
from
    (
        select
            date,
            type,
            send_id,
            receive_id,
            count(type) over (
                partition by
                    date,
                    type
            ) / count(type) over (
                partition by
                    date
            ) ration
        from
            email t1
            join user t2 on (send_id = t2.id)
            join user t3 on (t1.receive_id = t3.id)
        where
            t2.is_blacklist = 0
            and t3.is_blacklist = 0
    ) t1
where type = 'no_completed'
order by date