My Solution with join


  • 0
    M

      cast(number as decimal(9.2)) to make number to be rounded to two decimal places. IFNULL(number, 0) ----- If number is NULL, return 0

      Count the number of Trips without unbanned clients as newTotalTrips, and count the number of cancelled Trips without unbanned clients as newCacelledTrips.
      Then join these two new tables and add where clause which needs to make Request_at BETWEEN '2013-10-01' AND '2013-10-03'.

    select newTotalTrips.Request_at as Day,
    cast(IFNULL(newCacelledTrips.totalTrips,0)/newTotalTrips.totalTrips as decimal(9,2))
    as 'Cancellation Rate'
    from
    (select count() as totalTrips, t1.Request_at
    from (select t.

    from Trips as t
    inner join Users as u1 on t.Client_Id = u1.Users_Id and u1.Banned = 'No')
    as t1
    group by Request_at) as newTotalTrips>! Spoiler

    left join
    (select count() as totalTrips, t2.Request_at
    from (select t.

    from Trips as t
    inner join Users as u1 on t.Client_Id = u1.Users_Id and u1.Banned = 'No')
    as t2
    where t2.Status like '%cancelled%'
    group by t2.Request_at) as newCacelledTrips

    on newTotalTrips.Request_at = newCacelledTrips.Request_at
    where newTotalTrips.Request_at BETWEEN '2013-10-01' AND '2013-10-03'


Log in to reply
 

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