HardNo. 0262

Trips and Users — LeetCode 262 Python Solution

Topics
Database
Reading time
8 min

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

PythonSolution
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

Time
O(n log n)(typical)
Space · auxiliary
O(n)

Related problems

Related problems
No.ProblemDifficulty
0175Combine Two TablesEasy
0176Second Highest SalaryMedium
0177Nth Highest SalaryMedium
0178Rank ScoresMedium
0180Consecutive NumbersMedium
0181Employees Earning More Than Their ManagersEasy

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.

Stuck on problems like this in a live interview?

Stealth Interview is a desktop app for macOS and Windows. It reads the problem off your screen and returns a working solution with a step-by-step explanation and its time and space complexity, invisible to screen sharing.

Free to download. Check it runs on your machine, then pick a plan.