Accepted solution


  • 0
    L
    SELECT    
        A.Day as Day,   
        IFNULL(ROUND( B.target / A.Total , 2),0) as 'Cancellation Rate'
    FROM   
        (select  Ta.Request_at as Day ,count(*) as Total  from 
        Trips Ta left join Users  Tb on Ta.Client_Id = Tb.Users_Id  where Tb.Banned ='No' and Ta.Request_at >="2013-10-01"
         and Ta.Request_at <="2013-10-03" group by Ta.Request_at) A left join
        (select Ta.Request_at as Day,count(*) as target from
        Trips Ta left join Users  Tb on Ta.Client_Id = Tb.Users_Id where Tb.Banned = 'No' and Ta.Status in('cancelled_by_driver','cancelled_by_client')
        and Ta.Request_at >="2013-10-01" and Ta.Request_at <="2013-10-03" group by Ta.Request_at) B
    on A.Day =B.Day group by A.Day
    

  • 0
    L
    This post is deleted!

  • 0
    L

    @LearntobeCoder said in Accepted solution:

    @LearntobeCoder said in Accepted solution:

    SELECT    
        A.Day as Day,   
        IFNULL(ROUND( B.target / A.Total , 2),0) as 'Cancellation Rate'   #avoid 0.00 can't display
    FROM   
        (select  Ta.Request_at as Day ,count(*) as Total  from 
        Trips Ta left join Users  Tb on Ta.Client_Id = Tb.Users_Id  where Tb.Banned ='No' and Ta.Request_at >="2013-10-01"
         and Ta.Request_at <="2013-10-03" group by Ta.Request_at) A #count totall trips by day
    

    left join

    (select Ta.Request_at as Day,count(*) as target from
    Trips Ta left join Users  Tb on Ta.Client_Id = Tb.Users_Id where Tb.Banned = 'No' and Ta.Status in('cancelled_by_driver','cancelled_by_client')
    and Ta.Request_at >="2013-10-01" and Ta.Request_at <="2013-10-03" group by Ta.Request_at) B  /*count trips be cancelled by day*/on A.Day =B.Day group by A.Day
    

Log in to reply
 

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