One Understandable Method


  • 0
    I

    First, we need to find the qualified users.

    SELECT *
        FROM Users
        WHERE Banned = 'No'
                AND Role = 'client'
    

    Then, we need to join these users with the trip table.

    SELECT *
        FROM Users
        WHERE Banned = 'No'
                AND Role = 'client' AS Users
    JOIN Trips
        ON Trips.Client_Id = Users.Users_Id
            AND (Trips.Request_at
        BETWEEN '2013-10-01'
            AND '2013-10-03')
    

    In the end, we could calculate the cancellation rate based on corresponding dates.
    (MySQL)

    SELECT Request_at AS Day,
             ROUND(sum(CASE Status
        WHEN 'cancelled_by_client' THEN
        1
        WHEN 'cancelled_by_driver' THEN
        1
        ELSE 0 END)/count(Request_at),2) AS 'Cancellation Rate'
    FROM 
        (SELECT *
        FROM Users
        WHERE Banned = 'No'
                AND Role = 'client') AS Users
    JOIN Trips
        ON Trips.Client_Id = Users.Users_Id
            AND (Trips.Request_at
        BETWEEN '2013-10-01'
            AND '2013-10-03')
    GROUP BY  Trips.Request_at;
    

Log in to reply
 

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