My naive solution


  • 0
    E
    select t1.date1, round(cnt1*1.0/cnt2, 2)
    from 
    #1st sub query to get number of cancelled trip
    (select IFNULL(t.cnt1,0) as cnt1, r.Request_at as date1
    from Trips r
    left join
    (select count(*) as cnt1, Request_at as date1
    from Trips t
    join Users u
    on t.Client_Id=u.Users_Id
    where u.Banned="No" and (t.Status="cancelled_by_driver" or t.Status="cancelled_by_client")
    group by Request_at) t
    on r.Request_at = t.date1
    group by t.date1) as t1
    #2nd sub query to get number of trip within date range
    join
    (select count(*) as cnt2, Request_at as date2
    from Trips t
    join Users u
    on t.Client_Id=u.Users_Id
    where u.Banned="No"
    and (Request_at="2013-10-01" or Request_at="2013-10-02" or Request_at="2013-10-03")
    group by Request_at) as t2
    on t1.date1=t2.date2
    

    This solution is naive but should be easy to understand


  • 0
    D

    Write your MySQL query statement below

    select Day, round(sum(case when RecordStatus in ('cancelled_by_driver', 'cancelled_by_client') then 1 else 0 end) / count(*), 2)
    as 'Cancellation Rate'
    from (
    select A.Request_at as Day, A.status as RecordStatus
    from Trips A, Users B where A.Client_id = B.Users_Id
    and B.Role = 'client' and B.Banned = 'No'
    and Request_at >= '2013-10-01' and Request_at <'2013-10-04'
    ) temptable
    group by Day


Log in to reply
 

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