Hi, I think what the author tries to mean is that:
(1) find out the requests made by unbanned clients ( because the trip is requested by client)
(2) calculate the cancellation ratio (either by client or driver)
Basically it means to count all the rows where 'completed' column value is not 'completed' and sum(1) means to sum all the rows with 1 on each row no matter what, which you will get a cancellation rate which roughly meets the requirement, and then round the result to 2 float digits using round.
( (SUM(Status like 'cancelled_by_%') * 1.0)
/ SUM((Status = 'completed') + (Status like 'cancelled_by_%'))
, 2) as Rate
from Trips t, Users u
where t.Client_Id = u.Users_Id AND Banned = 'No' AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
group by Request_at
SELECT T.Request_at AS Day, ROUND(SUM(IF(T.Status != 'completed', 1, 0))/ COUNT(*), 2) AS `Cancellation Rate`
FROM Trips T JOIN Users U ON T.client_id = U.Users_Id JOIN Users DU ON T.driver_id = DU.Users_Id
WHERE U.Banned = 'No' AND DU.Banned = 'No' AND T.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.Request_at
select Day, round(sum(case when RecordStatus in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) / count(*), 2)
as 'Cancellation Rate'
select A.Request_at as Day, A.status as RecordStatus
from Trips A, Users B where A.Client_id = B.Users_Id
and B.Role = 'client' and B.Banned = 'No'
and Request_at >= '2013-10-01' and Request_at <'2013-10-04'
group by Day
I never tested the below code, but fixed some errors:
You used left join, so handle NULL cases.
Count returns INT type, cast it into Float
A and B should happen on the same date.
select A.Request_at AS Day, CAST(IFNULL(count(B.id), 0)/count(A.id) AS DECIMAL(8,2)) AS 'Cancellation Rate'
from Trips A
on (A.Client_id = Users.Users_id AND A.Request_At = B.Request_at)
left join (select Request_at, Id from Trips
where Trips.status like 'cancelled_by_%') B
on A.Id = B.Id
where Users.Banned = 'No'
and A.Request_at between '2013-10-01' and '2013-10-03'
group by A.Request_at