Sharing my solution,


  • 30
    S
    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

  • 0
    C

    select Request_at,
    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
    ;


  • 0
    K
    This post is deleted!

  • 0
    S

    /count(),2) Rate --> / count(), 2) AS "Cancellation Rate"


  • 0
    N

    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?


  • 3
    K

    another version.

    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

  • 0
    N

    why use COUNT(Status <> 'completed' OR NULL) ? I try to use COUNT(Status <> 'completed') it is not correct. why the or null is necessary?


  • 0
    K

    because any none null value(include false and 0) will be counted


  • 0
    N

    Oh,i see,thanks


  • 0
    C

    @kxcf Why can't we replace COUNT(Status <> 'completed' OR NULL) with count(t.Status like 'cancelled%')? I tried but it failed.


  • 1
    K

    "round(sum(case when t.Status like 'cancelled_%' then 1 else 0 end)/count(*),2) Rate" is the point!!!


  • 0
    C

    @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.


  • 0
    C

    @cimyntt because any none null value(include false and 0) will be counted


  • 8
    H

    Why should we count requests cancelled by driver? The requirement in this question is "find the cancellation rate of requests made by unbanned clients"


  • 0
    K

    @simplyida said in Sharing my solution,:

    SELECT t.Request_at AS Day,
    round(COUNT(IF(Status like 'cancelled_by_%' ,TRUE,NULL)) / COUNT(*) ,2) AS 'Cancellation Rate' FROM Trips t 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


  • 2
    Y
    1. 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


  • 0
    G
    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
    ;
    

    Confusing question

    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!!!


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.