A Basic way to slove


  • 0
    Q

    SELECT tb2.Request_at AS Day, round(tb2.Asum / tb3.total, 2) AS 'Cancellation Rate'
    FROM (SELECT DISTINCT trips.Request_at, ifnull(tb1.sum, 0) AS Asum
    FROM trips LEFT JOIN (SELECT COUNT(client_id) AS sum, Request_at AS day
    FROM trips
    WHERE client_id IN (SELECT users_id
    FROM users
    WHERE banned = 'NO')
    AND (status = 'cancelled_by_client'
    OR status = 'cancelled_by_driver')
    GROUP BY Request_at
    ) tb1 ON trips.Request_at = tb1.day
    ORDER BY trips.Request_at
    ) tb2, (SELECT COUNT(*) AS total, Request_at
    FROM Trips
    WHERE client_id IN (SELECT users_id
    FROM users
    WHERE banned = 'NO')
    GROUP BY Request_at
    ) tb3
    WHERE tb2.Request_at = tb3.Request_at
    AND tb2.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.