Watch 10 video solutions for Trips and Users, a hard level problem involving Database. This walkthrough by Ankit Bansal has 40,350 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | varchar |
+-------------+----------+
id is the primary key (column with unique values) for this table.
The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are foreign keys to the users_id at the Users table.
Status is an ENUM (category) type of ('completed', 'cancelled_by_driver', 'cancelled_by_client').
Table: Users
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id is the primary key (column with unique values) for this table.
The table holds all users. Each user has a unique users_id, and role is an ENUM type of ('client', 'driver', 'partner').
banned is an ENUM (category) type of ('Yes', 'No').
The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.
Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03". Round Cancellation Rate to two decimal points.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Trips table: +----+-----------+-----------+---------+---------------------+------------+ | 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 | 6 | completed | 2013-10-01 | | 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | | 5 | 1 | 10 | 1 | completed | 2013-10-02 | | 6 | 2 | 11 | 6 | completed | 2013-10-02 | | 7 | 3 | 12 | 6 | completed | 2013-10-02 | | 8 | 2 | 12 | 12 | completed | 2013-10-03 | | 9 | 3 | 10 | 12 | completed | 2013-10-03 | | 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | +----+-----------+-----------+---------+---------------------+------------+ Users table: +----------+--------+--------+ | users_id | banned | role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+ Output: +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+ Explanation: On 2013-10-01: - There were 4 requests in total, 2 of which were canceled. - However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation. - Hence there are 3 unbanned requests in total, 1 of which was canceled. - The Cancellation Rate is (1 / 3) = 0.33 On 2013-10-02: - There were 3 requests in total, 0 of which were canceled. - The request with Id=6 was made by a banned client, so it is ignored. - Hence there are 2 unbanned requests in total, 0 of which were canceled. - The Cancellation Rate is (0 / 2) = 0.00 On 2013-10-03: - There were 3 requests in total, 1 of which was canceled. - The request with Id=8 was made by a banned client, so it is ignored. - Hence there are 2 unbanned request in total, 1 of which were canceled. - The Cancellation Rate is (1 / 2) = 0.50
Problem Overview: You are given two tables: Trips and Users. Each trip has a client, driver, status, and request date. Some users may be banned. The task is to compute the daily cancellation rate for trips between specific dates while excluding trips where either the client or driver is banned.
The cancellation rate is defined as the number of cancelled trips divided by the total number of valid trips on that day. Only trips requested between 2013-10-01 and 2013-10-03 are considered, and both the client and driver must not be banned.
Approach 1: SQL Join and Filtering (Time: O(n), Space: O(1))
This approach joins the Trips table with the Users table twice: once to check the client and once to check the driver. The join allows you to filter out any records where either user has banned = 'Yes'. After filtering, iterate over the remaining rows grouped by request_at. Use conditional logic such as CASE WHEN status != 'completed' to count cancelled trips and divide by the total trips for that day. Since SQL engines scan the relevant rows once and perform grouping, the time complexity is effectively O(n) with constant auxiliary space.
This method is the most direct because it models the real relationship between tables. The key insight is recognizing that you must validate both participants of the trip. A double join combined with a date filter and aggregation ensures only eligible trips contribute to the final rate.
Approach 2: Data Filtering and Aggregation (Time: O(n), Space: O(1))
This version emphasizes filtering first, then computing aggregates. Instead of embedding all logic inside one aggregation query, you conceptually reduce the dataset by selecting trips whose client and driver IDs correspond to non‑banned users. After the dataset is filtered, perform a GROUP BY request_at and compute two aggregates: total trips and cancelled trips. The cancellation rate is calculated using ROUND(cancelled / total, 2) to match the expected output format.
The main idea is separating eligibility filtering from metric computation. This makes the query easier to reason about when datasets grow larger or when additional constraints are added. Databases optimize grouping and conditional counting efficiently, so the complexity remains linear relative to the number of trip records.
Both approaches rely heavily on concepts from database queries, particularly SQL joins and aggregation. Understanding how joins expand rows and how GROUP BY collapses them into summary metrics is the core skill tested here.
Recommended for interviews: The join-and-filter approach is what interviewers typically expect. Showing the correct joins proves you understand relational data modeling. Aggregating with GROUP BY and conditional counting demonstrates practical SQL skills used in analytics and backend systems.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join and Filtering | O(n) | O(1) | Best when validating relationships across multiple tables and computing metrics per group. |
| Data Filtering and Aggregation | O(n) | O(1) | Useful when you want a clearer pipeline: filter valid rows first, then compute aggregates. |