Skip to main content
Talentd

Trips and Users - Solution & Explanation

HardDatabase12 min readAsked at: Amazon, Microsoft, Uber +2
Practice this problem

Problem Statement

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

Approach Overview

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 1: Using SQL Join and Filtering

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.

Code

CC++JavaPythonC#JavaScript

C

C++

Java

Python

C#

JavaScript

Complexity

Time Complexity: Not applicable
Space Complexity: Not applicable

Try this approach in the editor →

Approach 2: Using Data Filtering and Aggregation

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.

Code

CC++JavaPythonC#JavaScript

C

C++

Java

Python

C#

JavaScript

Complexity

Time Complexity: Not applicable
Space Complexity: Not applicable

Try this approach in the editor →

Approach 3: Default Approach

Code

PythonMySQL

Python

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Using SQL Join and Filtering

Time Complexity: Not applicable
Space Complexity: Not applicable

Using Data Filtering and Aggregation

Time Complexity: Not applicable
Space Complexity: Not applicable

Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL Join and FilteringO(n)O(1)Best when validating relationships across multiple tables and computing metrics per group.
Data Filtering and AggregationO(n)O(1)Useful when you want a clearer pipeline: filter valid rows first, then compute aggregates.

Video Solution

Leetcode Hard Problem | Complex SQL 7 | Trips and Users • Ankit Bansal • 52,092 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Trips and Users easy or hard?
Trips and Users is categorized as a Hard database problem because it requires combining multiple SQL concepts: joins across tables, filtering based on user attributes, grouping results by date, and computing a derived metric with conditional aggregation.
Trips and Users Python/Java solution
Although the problem is fundamentally a SQL query challenge, the logic can be implemented in languages like Python or Java by filtering trip records, validating users against a banned set, grouping by date, and computing cancellation ratios using hash maps.
How to solve Trips and Users in O(n)?
Filter trips by joining with the Users table to exclude banned clients and drivers. Restrict rows to the required date range, then group by request_at. Use a CASE expression or conditional COUNT to calculate cancelled trips and divide by total trips per day.
What is the best approach for Trips and Users?
The most common solution uses SQL joins with filtering and aggregation. Join the Trips table with the Users table twice to ensure both the client and driver are not banned, filter by the date range, then group by request_at. Compute the cancellation rate using conditional counting of non-completed trips divided by total trips.
Is Trips and Users asked at Google/Amazon/Meta?
Database aggregation and SQL join problems similar to Trips and Users appear in interviews at companies like Google, Amazon, and Meta. They test the ability to reason about relational data, filtering conditions, and computing metrics using GROUP BY.
What data structure is used in Trips and Users?
The problem relies on relational database tables and SQL operations rather than traditional in-memory data structures. Key concepts include table joins, filtering conditions, and aggregation functions such as COUNT and GROUP BY.
What is the time complexity of Trips and Users?
The query runs in O(n) time relative to the number of rows in the Trips table. The database scans the filtered rows, performs joins with indexed user IDs, and aggregates results using GROUP BY. Space complexity is O(1) aside from internal query execution buffers.

Ready to solve this problem?

Practice Trips and Users with our built-in code editor and test cases.

Practice on FleetCode
Previous

Hopper Company Queries I

Next

Hopper Company Queries II