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: The Trips and Users database problem asks you to compute the daily cancellation rate of trips where both the client and driver are not banned. Only trips between 2013-10-01 and 2013-10-03 are considered. The result groups by request date and calculates cancelled_trips / total_valid_trips.
Approach 1: Using SQL Join and Filtering (O(n) time, O(1) extra space)
The most common solution joins the Trips table with the Users table twice: once for the client and once for the driver. The joins allow you to filter out banned users by checking banned = 'No'. After filtering, restrict rows to the required date range and group by request_at. The cancellation rate is computed using conditional counting: count trips where status != 'completed' and divide by total trips for that day. This approach relies heavily on SQL joins and row-level filtering.
Because the database scans the filtered rows once and aggregates with GROUP BY, the effective complexity is O(n) with respect to the number of trips. Space usage remains O(1) outside the grouped output. This method mirrors how production analytics queries work in relational databases.
Approach 2: Data Filtering and Aggregation (O(n) time, O(1) extra space)
This approach focuses on filtering valid trips first, then computing the cancellation ratio through aggregation. Instead of thinking in terms of joins first, treat the dataset as a filtered stream of valid trip records. Remove rows where either the client or driver is banned, limit the dataset to the three required dates, then apply conditional aggregation. For example, count cancelled trips with a conditional expression and divide by the total count in the same group.
The key insight is using SUM(CASE WHEN ... THEN 1 ELSE 0 END) or similar constructs to compute the numerator directly during aggregation. This avoids multiple passes and keeps the query compact. Complexity remains O(n) time since each trip is evaluated once, and O(1) additional space apart from grouped results. The technique is a classic pattern in database analytics and SQL aggregation queries.
Recommended for interviews: The SQL Join and Filtering approach is what most interviewers expect. It clearly demonstrates understanding of relational joins, filtering conditions, and grouped metrics. Showing the aggregation trick for counting cancelled trips proves you understand how to compute ratios efficiently inside SQL.
This approach involves using SQL joins between the 'Trips' and 'Users' tables to filter out trips involving banned users. Then, group the results by date and count the total and cancelled trips to compute the cancellation rate for each day.
C is not typically used for direct database querying tasks like this. A common approach would be to use an embedded SQL (e.g., SQLite) or call database APIs.
C++
Java
Python
C#
JavaScript
Time Complexity: Not applicable
Space Complexity: Not applicable
This approach involves extracting trip data and user data, performing filtering operations in the application logic to compute stats, and then using aggregation to calculate the cancellation rate for each day.
C does not natively support high-level database operations, making it unsuitable for this approach unless supplemented by an SQL library.
C++
Java
Python
C#
JavaScript
Time Complexity: Not applicable
Space Complexity: Not applicable
| Approach | Complexity |
|---|---|
| Using SQL Join and Filtering | Time Complexity: Not applicable |
| Using Data Filtering and Aggregation | Time Complexity: Not applicable |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join and Filtering | O(n) | O(1) | Best general solution. When relational joins are required to filter banned users. |
| Data Filtering and Aggregation | O(n) | O(1) | When computing metrics directly with conditional aggregation using GROUP BY. |
Leetcode Hard Problem | Complex SQL 7 | Trips and Users • Ankit Bansal • 40,350 views views
Watch 9 more video solutions →Practice Trips and Users with our built-in code editor and test cases.
Practice on FleetCode