leetcode題:
我的code:
select whole.Day, convert(if(cls is null,0,cls/_all),decimal(15,2)) as `Cancellation Rate`
from (select count(NoBan.Status) as cls,NoBan.Request_at as Day
from( select *
from Trips,
(select Users_Id as cId,Banned as ban1 from Users where Role="client") as U1,
(select Users_Id as dId,Banned as ban2 from Users where Role="driver") as U2
where U1.cId = Trips.Client_Id and U1.ban1 <> "yes"
and U2.dId = Trips.Driver_Id and U2.ban2 <> "yes") as NoBan
where NoBan.Status = "cancelled_by_driver" or NoBan.Status = "cancelled_by_client"
group by NoBan.Request_at) as cls right join
(select count(NoBan.Status) as _all, NoBan.Request_at as Day
from( select *
from Trips,
(select Users_Id as cId,Banned as ban1 from Users where Role="client") as U1,
(select Users_Id as dId,Banned as ban2 from Users where Role="driver") as U2
where U1.cId = Trips.Client_Id and U1.ban1 <> "yes"
and U2.dId = Trips.Driver_Id and U2.ban2 <> "yes") as NoBan
group by NoBan.Request_at) as whole on whole.day = cls.day
where whole.Day = "2013-10-01" or whole.Day = "2013-10-02" or whole.Day = "2013-10-03"
