What is wrong in my code?


  • 0
    S
    select Request_at as Day , 
    CASE 
        when Max(cancel_Time) = 0 or Max(cancel_Time) is NULL then round(0,2)
        when Max(request_Time) = 0 or Max(request_Time) is NULL then round(0,2)
        when Max(cancel_Time) <> 0 and Max(request_Time) <> 0 then round(Max(cancel_Time) / Max(request_Time),2)
    end as "Cancellation Rate"
    from
    (select Trips.Request_at , 
        CASE
            when @pre_date = Trips.Request_at then @total_cnt := @total_cnt + 1 
            when @pre_date <> Trips.Request_at then @total_cnt := 1 
        END as request_Time , 
        CASE
            when @pre_date = Trips.Request_at and Trips.STATUS = 'cancelled_by_driver' then @cancel_cnt := @cancel_cnt + 1 
            when @pre_date = Trips.Request_at and Trips.STATUS = 'cancelled_by_client' then @cancel_cnt := @cancel_cnt + 1 
            when @pre_date = Trips.Request_at and Trips.STATUS = 'completed' then @cancel_cnt
            when @pre_date <> Trips.Request_at and Trips.STATUS = 'cancelled_by_driver' then @cancel_cnt := 1 
            when @pre_date <> Trips.Request_at and Trips.STATUS = 'cancelled_by_client' then @cancel_cnt := 1 
            when @pre_date <> Trips.Request_at and Trips.STATUS = 'completed' then @cancel_cnt := 0 
        END as cancel_Time , 
        (@pre_date := Trips.Request_at) as d
    from Trips join Users on Trips.Client_Id = Users.Users_Id  , (select @pre_date = -1 , @total_cnt := 0 , @cancel_cnt :=0) r 
    where Users.Banned = 'No' and Trips.Request_at >= '2013-10-01' and Trips.Request_at <= '2013-10-03'
    order by Trips.Request_at) A group by Request_at;
    

    I passed 8 cases but failed the last one:

    Input:
    {"headers": {"Trips": ["Id", "Client_Id", "Driver_Id", "City_Id", "Status", "Request_at"], "Users": ["Users_Id", "Banned", "Role"]}, "rows": {"Trips": [["1", "1", "10", "1", "completed", "2013-10-01"], ["2", "2", "11", "1", "cancelled_by_driver", "2013-10-01"], ["3", "3", "12", "6", "completed", "2013-10-01"], ["4", "4", "13", "6", "cancelled_by_client", "2013-10-01"], ["5", "1", "10", "1", "completed", "2013-10-02"], ["6", "2", "11", "6", "completed", "2013-10-02"], ["7", "3", "12", "6", "completed", "2013-10-02"], ["8", "2", "12", "12", "completed", "2013-10-03"], ["9", "3", "10", "12", "completed", "2013-10-03"], ["10", "4", "13", "12", "cancelled_by_driver", "2013-10-03"], ["11", "3", "10", "6", "cancelled_by_driver", "2013-10-02"], ["12", "5", "12", "12", "cancelled_by_client", "2013-10-01"], ["13", "4", "13", "1", "cancelled_by_driver", "2013-10-03"]], "Users": [["1", "No", "client"], ["2", "Yes", "client"], ["3", "No", "client"], ["4", "No", "client"], ["5", "Yes", "client"], ["10", "No", "driver"], ["11", "No", "driver"], ["12", "No", "driver"], ["13", "No", "driver"]]}}

    Output:
    {"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.50], ["2013-10-03", 0.50]]}

    Expected:
    {"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-01", 0.33], ["2013-10-02", 0.33], ["2013-10-03", 0.67]]}


Log in to reply
 

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