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
The key idea in #262 Trips and Users is to calculate the daily cancellation rate for ride requests while excluding trips involving banned users. The solution requires combining the Trips and Users tables to ensure that both the client and driver participating in a trip are not banned.
Start by joining the Trips table with the Users table twice—once for the client and once for the driver. Filter rows where both users have banned = 'No' and restrict the date range using the request_at field. Next, group the results by day and compute the cancellation rate using conditional aggregation. Count trips where the status indicates cancellation and divide by the total number of valid trips for that day.
Finally, format the output by rounding the cancellation rate to two decimal places. The query mainly relies on joins, filtering, grouping, and aggregate functions, making it efficient for typical relational database workloads.
| Approach | Time Complexity | Space Complexity |
|---|---|---|
| SQL Join + Aggregation | O(N) | O(1) |
Ankit Bansal
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.
Time Complexity: Not applicable
Space Complexity: Not applicable
1// C code is not applicable for database queries.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.
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.
Time Complexity: Not applicable
Space Complexity: Not applicable
1// C++ code is not applicable for database queries.Watch expert explanations and walkthroughs
Practice problems asked by these companies to ace your technical interviews.
Explore More ProblemsJot down your thoughts, approach, and key learnings
Yes, database problems similar to Trips and Users are commonly asked in technical interviews at large tech companies. They test a candidate's ability to write efficient SQL queries involving joins, filtering, and aggregations.
The optimal approach uses SQL joins and aggregation. Join the Trips table with the Users table for both clients and drivers, filter out banned users, and calculate the cancellation rate per day using conditional counts.
Key SQL concepts include INNER JOINs, filtering with WHERE conditions, GROUP BY for daily aggregation, and aggregate functions like COUNT and SUM. Conditional logic using CASE statements is also useful.
The cancellation rate is computed by dividing the number of cancelled trips by the total number of valid trips for a given day. SQL conditional aggregation or CASE expressions are typically used to count cancelled requests.
C++ does not natively support high-level database operations. Typically, a dedicated library or embedded SQL is used.