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

1.看到根据日期 分组排序 group by date order by date 

2.case when条件的使用

3.join on两个分区时要写两个语句