How hard is LeetCode 262. Trips and Users?

LeetCode 262. Trips and Users is rated Hard on LeetCode.

What topics does LeetCode 262. Trips and Users cover?

LeetCode 262. Trips and Users is tagged Database on LeetCode.

Trips and Users — LeetCode 262 Python Solution

HardDatabase

Problem: #262
Reading time: 8 min
Source: leetcode.com

The problem

For each day from 2013-10-01 to 2013-10-03, work out the cancellation rate among requests where neither the client nor the driver is banned: the number of those requests that were cancelled, divided by the number of those requests, rounded to two decimal places. Trips has id (int, primary key), client_id and driver_id (int, both referencing Users.users_id), city_id (int), status (one of completed, cancelled_by_driver and cancelled_by_client) and request_at (varchar holding the date); Users has users_id (int, primary key), banned (Yes or No) and role (client, driver or partner). Return Day and Cancellation Rate, and skip any day with no such requests.

Example

Input: Trips(id, client_id, driver_id, city_id, status, request_at) = (1, 1, 10, 1, 'completed', '2013-10-01'), (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01'), (3, 3, 12, 1, 'completed', '2013-10-01'), (4, 1, 10, 1, 'cancelled_by_client', '2013-10-02'), (5, 2, 11, 1, 'completed', '2013-10-02'), (6, 3, 12, 1, 'completed', '2013-10-02'), (7, 4, 10, 1, 'cancelled_by_client', '2013-10-02'), (8, 1, 11, 1, 'completed', '2013-10-02'); Users(users_id, banned, role) = (1, 'No', 'client'), (2, 'No', 'client'), (3, 'No', 'client'), (4, 'Yes', 'client'), (10, 'No', 'driver'), (11, 'No', 'driver'), (12, 'No', 'driver')
Output: ('2013-10-01', 0.33), ('2013-10-02', 0.25)
Explanation: Trip 7 is excluded because its client is banned, leaving 1 cancellation out of 3 requests on the first day and 1 out of 4 on the second.

Python solution

Python

import pandas as pd


def trips_and_users(trips: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
    # 1) temporal filtering
    trips = trips[trips["request_at"].between("2013-10-01", "2013-10-03")].rename(
        columns={"request_at": "Day"}
    )

    # 2) filtering based not banned
    # 2.1) mappning the column 'banned' to `client_id` and `driver_id`
    df_client = (
        pd.merge(trips, users, left_on="client_id", right_on="users_id", how="left")
        .drop(["users_id", "role"], axis=1)
        .rename(columns={"banned": "banned_client"})
    )
    df_driver = (
        pd.merge(trips, users, left_on="driver_id", right_on="users_id", how="left")
        .drop(["users_id", "role"], axis=1)
        .rename(columns={"banned": "banned_driver"})
    )
    df = pd.merge(
        df_client,
        df_driver,
        left_on=["id", "driver_id", "client_id", "city_id", "status", "Day"],
        right_on=["id", "driver_id", "client_id", "city_id", "status", "Day"],
        how="left",
    )
    # 2.2) filtering based on not banned
    df = df[(df["banned_client"] == "No") & (df["banned_driver"] == "No")]

    # 3) counting the cancelled and total trips per day
    df["status_cancelled"] = df["status"].str.contains("cancelled")
    df = df[["Day", "status_cancelled"]]
    df = df.groupby("Day").agg(
        {"status_cancelled": [("total_cancelled", "sum"), ("total", "count")]}
    )
    df.columns = df.columns.droplevel()
    df = df.reset_index()

    # 4) calculating the ratio
    df["Cancellation Rate"] = (df["total_cancelled"] / df["total"]).round(2)
    return df[["Day", "Cancellation Rate"]]

Complexity

Measure	Complexity
Time	O(n log n) (typical)
Space	O(n) auxiliary

Frequently asked questions

How hard is LeetCode 262. Trips and Users?: LeetCode 262. Trips and Users is rated Hard on LeetCode.
What topics does LeetCode 262. Trips and Users cover?: LeetCode 262. Trips and Users is tagged Database on LeetCode.