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

select q.date,round(sum(if(type = 'no_completed',1,0))/count(q.date),3) as p
from (
    select *
    from email
    where send_id in (
        select id
        from user
        where is_blacklist != 1
    ) and receive_id in (
        select id
        from user
        where is_blacklist != 1
    ) 
) q
group by q.date