题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
先筛选出所有正常用户发送到正常用户的邮件信息,再统计失败的比例
with deled_email as( select *, (case when type='no_completed' then 1 else 0 end) as yes from email where send_id not in (select id from user where is_blacklist=1) and receive_id not in (select id from user where is_blacklist=1) ) select date, FORMAT(sum(yes) / count(id),3) from deled_email group by date order by date asc
也可以直接在group by前进行筛选,不用建新表