select t.Request_at Day, round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) Rate from Trips t inner join Users u on t.Client_Id = u.Users_Id and u.Banned='No' where t.Request_at between '2013-10-01' and '2013-10-03' group by t.Request_at
( (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
May I know why Sum() is used here? and then divide by count(*)
Isn't the rate to display shd map exactly to get 0.33/0.00/0.50 as per the date request made as per the question?
I observe the logic if there are request made at all 3 days its 0.33, 2 days 0.50, etc. But, I donot see how it satisfies the question requirements?
SELECT Request_at AS Day , ROUND(COUNT(Status <> 'completed' OR NULL) / COUNT(*), 2) AS `Cancellation Rate` FROM Trips JOIN Users ON Users_Id = Client_Id AND Banned = 'No' AND Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at
COUNT(Status <> 'completed' OR NULL) ? I try to use
COUNT(Status <> 'completed') it is not correct. why the
or null is necessary?
@kxcf Why can't we replace COUNT(Status <> 'completed' OR NULL) with count(t.Status like 'cancelled%')? I tried but it failed.
"round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) Rate" is the point!!!
@naveen.m8 you maybe know some sql excution order and most important to understand the question.
In my understanding. By the way, ROUND(COUNT(Status <> 'completed' OR NULL) / COUNT(), 2) AS
Cancellation Rate* this statement will excute after group by Request_at, so it just calcute per day.
@cimyntt because any none null value(include false and 0) will be counted
Why should we count requests cancelled by driver? The requirement in this question is "find the cancellation rate of requests made by unbanned clients"
Statuslike 'cancelled_by_%' ,TRUE,NULL)) / COUNT(*) ,2) AS 'Cancellation Rate' FROM
Usersu ON t.
Banned='No' where t.Request_at between '2013-10-01' and '2013-10-03' GROUP BY t.
condition should be cancelled_by_client, since the requirement is to calculate cancelled by client
2.when join, should have common request_at
select request_at "Day", (select count(*) from users, trips where request_at = t.request_at and users_id = client_id and role = 'client' and banned = 'No' and status like 'cancelled_by_client') / count(*)"Cancellation Rate"
from trips t
where to_date(t.request_at, 'yyyy-mm-dd') between '01-Oct-13' and '04-Oct-13'
group by request_at
Answer: 0.25, 0, 0
select Request_at as 'Day', (select count(t.Client_Id) from Users u join Trips t on t.Client_Id = u.Users_Id where t.Status = 'cancelled_by_client' and u.Banned = 'No' and u.Role = 'client')/(count(*)) as 'Calcellation Rate' from Trips where Request_at in ('2013-10-01', '2013-10-02', '2013-10-03') group by Request_at ;
I have to say I don't understand the question, Many answers... I think...
My result is different, lost myself, by the way, the cancellation rate is very confusing, do i need to include the cancelled by driver??? made by clients....
Even my code I don't know what's going on...
Somebody who can give some explaination please!!!
@simplyida Hi, Why do you need to specify
where t.Request_at between '2013-10-01' and '2013-10-03'
It shows the right results without this line too.
Select t.request_at Day,
round( sum(Case when t.status like 'cancel%' then '1' else '0' end)
/count(t.status), 2) Rate
from trips t inner join users u on t.client_id=u.users_id and u.banned='No'
group by request_at
Thanks in advance!
@simplyida this will not result in 2nd October row. For that you need a join with a calendar table.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.