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

-- round(a,3) 3位小数
-- coalesce(a) 防止计算返回null
sum()/count() 窗口函数

select
    e.date,
    round(
        coalesce(
            sum(
                case
                    when e.type = 'no_completed' then 1
                    else 0
                end
            ) / count(e.type)
        ),
        3
    ) as p
from
    email as e
where
    e.send_id in (
        select
            id
        from
            user as u
        where
            u.is_blacklist = 0
    )
    and e.receive_id in (
        select
            id
        from
            user as u
        where
            u.is_blacklist = 0
    )
group by
    e.date
order by
    e.date asc;