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

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