写一段SQL语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表，你的SQL语句应返回如下结果，取消率(Cancellation Rate）保留两位小数。

 

建表语言： 

create table Trips
(id NUMBER(5),
Client_id NUMBER(5),
driver_id NUMBER(5),
city_id NUMBER(5),
status varchar2(50),
request_at date);
insert into Trips values(1,1,10,1,'completed',TO_DATE('20131001','YYYYMMDD'));   
insert into Trips values(2,2,11,1,'cancelled_by_driver',TO_DATE('20131001','YYYYMMDD'));
insert into Trips values(3,3,12,6,'completed',TO_DATE('20131001','YYYYMMDD'));
insert into Trips values(4,4,13,6,'cancelled_by_client',TO_DATE('20131001','YYYYMMDD'));   
insert into Trips values(5,1,10,1,'completed',TO_DATE('20131002','YYYYMMDD'));
insert into Trips values(6,2,11,6,'completed',TO_DATE('20131002','YYYYMMDD'));   
insert into Trips values(7,3,12,6,'completed',TO_DATE('20131002','YYYYMMDD'));   
insert into Trips values(8,2,12,12,'completed',TO_DATE('20131003','YYYYMMDD'));   
insert into Trips values(9,3,10,12,'completed',TO_DATE('20131003','YYYYMMDD'));    
insert into Trips values(10,4,13,12,'cancelled_by_driver',TO_DATE('20131003','YYYYMMDD'));  
SELECT * FROM TRIPS;

create table USERS
(USERS_id NUMBER(5),
BANNED varchar2(10),
ROLE varchar2(10));
insert into users values(1,  'No',      'client');
insert into users values(2,  'Yes',     'client');   
insert into users values(3,  'No',      'client');   
insert into users values(4,  'No',      'client');   
insert into users values(10,  'No',      'driver');   
insert into users values(11,  'No',      'driver');   
insert into users values(12,  'No',      'driver');   
insert into users values(13,  'No',      'driver') ;
SELECT * FROM USERS;

查询语言：

SELECT REQUEST_AT AS DAY,
  ROUND(SUM(CASE WHEN STATUS='cancelled_by_client' 
  OR STATUS='cancelled_by_driver' THEN 1 ELSE 0 END)/COUNT(*),2)
FROM TRIPS T
LEFT JOIN (SELECT * FROM USERS WHERE BANNED='No' AND ROLE='client') U1
ON T.CLIENT_ID=U1.USERS_ID
LEFT JOIN (SELECT * FROM USERS WHERE BANNED='No' AND ROLE='driver') U2
ON T.DRIVER_ID=U2.USERS_ID
WHERE REQUEST_AT BETWEEN TO_DATE('20131001','YYYYMMDD') 
  AND TO_DATE('20131003','YYYYMMDD') 
GROUP BY REQUEST_AT 
ORDER BY REQUEST_AT;