SQL实战65.异常的邮件概率

题目

解答

自己的思路

联结三张表作为t
分母

select count(t.type)
from t
where t.r_s=0 and t.r_r = 0
group by t.date

分子

select count(t.type)
from t
where t.r_s=0 and t.r_r = 0 and t.type="no_completed"
group by t.date

最后分子分母相除

借鉴思路

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