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


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


  • 0
    A

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


  • 0
    A

    @simplyida this will not result in 2nd October row. For that you need a join with a calendar table.


  • 0
    G
    This post is deleted!

Log in to reply
 

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