题解 | #异常的邮件概率#
异常的邮件概率
http://www.nowcoder.com/practice/d6dd656483b545159d3aa89b4c26004e
With t as (Select date, count(id) as num From email
Where send_id in (Select distinct id From user Where is_blacklist=0)
and receive_id in (Select distinct id From user Where is_blacklist=0)
Group by date ),
uc as (Select date, count(id) as num From email
Where type='no_completed' and send_id in (Select distinct id From user Where is_blacklist=0)
and receive_id in (Select distinct id From user Where is_blacklist=0)
Group by date )
Select t.date, Round((uc.num/t.num),3)as p
From t Join uc On t.date=uc.date
