Simple join + sum function solution

  • 4
    select t.Request_at as Day,
           round(sum(if(t.Status <> 'completed', 1, 0))/sum(1), 2) as 'Cancellation Rate'
    from Trips as t
    inner join Users as u on t.Client_id = u.Users_id and u.Banned <> 'Yes'
    where t.Request_at >= '2013-10-01' and t.Request_at <='2013-10-03'
    group by t.Request_at;

  • 0

    Perfect, i love this solution.

  • 0

    Hi Jinwu,

    I like your answer! but I was struggling to understand the use of "sum" instead of "count". Could you help to explain a little further?! I am appreciated!

  • 0

    Basically it means to count all the rows where 'completed' column value is not 'completed' and sum(1) means to sum all the rows with 1 on each row no matter what, which you will get a cancellation rate which roughly meets the requirement, and then round the result to 2 float digits using round.

Log in to reply

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