题解 | 异常的邮件概率

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


# select date,round(count(type = 'no_completed' or NULL)/count(1),3) as p
# from email e
# where not exists(
#     select id 
#     from user u 
#     where (id=e.send_id or id = e.receive_id) and is_blacklist =1
#     )
# group by date
# order by date ASC;


select date,round(sum(if(type = 'no_completed',1,0))/count(type),3) as p
from email e
where (
    select id
    from user
    where is_blacklist = 1 
) not in (send_id,receive_id)
group by date
order by date ASC;