牛客SQL搞人心态的一天

select
date,
cast( count(case when type='no_completed' then 1 else null end)*1.0 / count(id) as decimal(10,3)) 'p'
from email
where send_id in (select id from user where is_blacklist=0)
and receive_id in (select id from user where is_blacklist=0)
group by date;

两个in的效率不高，大规模数据可以用send_id和receive_id分别join两次user（虽然也挺亏的）
还有结果在sqlite跑不起来，小数点没有保留三位，虽然sqlite是可以用cast函数的。mysql结果正确。