Smart query accepted with 196 ms


  • 0
    C

    First you can apply the rules according to the definition of the problem. In this case, unbanned clients and the days between 10/01/2013 and 10/03/2013. You must use both tables for identifying these criteria and keep two columns: status (cancelled or not) and request_at (to identify each day).

    select b.status, b.request_at
    from users a join trips b on a.users_id = b.client_id
    where banned = 'No' and request_at between '2013-10-01' and '2013-10-03'

    Once this is done, you need to identify the cases where the request has been cancelled. A quick way to do this would be to create a column with a dummy variable (0,1), where 1 means that there was a cancellation and zero otherwise. To calculate the cancellation rate, you can create another column filled with 1, as you can see below:

    select
    c.request_at,
    if(c.status= "completed", 0, 1) as Cancelled,
    '1' as Total
    from (select b.status, b.request_at
    from users a join trips b on a.users_id = b.client_id
    where banned = 'No' and request_at between '2013-10-01' and '2013-10-03') c

    Now, you just need to calculate the cancellation rate for each day. You can sum each column "Cancelled" and "Total" and divide the sum of "Cancelled" by sum of "Total" to find the cancellation rate. As you need to do it for each day, you must apply group by "Day". To enter two digits, you can use the round function, as you can see below:

    select d.request_at as Day,
    round(sum(d.Cancelled)/sum(d.Total),2) as 'Cancellation Rate'
    from (select
    c.request_at,
    if(c.status= "completed", 0, 1) as Cancelled,
    '1' as Total
    from (select b.status, b.request_at
    from users a join trips b on a.users_id = b.client_id
    where banned = 'No' and request_at between '2013-10-01' and '2013-10-03') c) d
    group by d.request_at

    Result:

    +------------+-------------------+
    | Day | Cancellation Rate |
    +------------+-------------------+
    | 2013-10-01 | 0.33 |
    | 2013-10-02 | 0.00 |
    | 2013-10-03 | 0.50 |
    +------------+-------------------+


Log in to reply
 

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