262. Trips and Users Solution - SQL Explanation

The problem

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.

Python

import sqlite3

# Assuming connection and tables setup
connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

query = '''
SELECT t.request_at AS Day,
       ROUND(SUM(CASE WHEN t.status != 'completed' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 2) AS CancellationRate
FROM Trips t
JOIN Users c ON t.client_id = c.users_id AND c.banned = 'No'
JOIN Users d ON t.driver_id = d.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at;
'''

cursor.execute(query)
results = cursor.fetchall()
for row in results:
    print(f"{row[0]}: {row[1]}")

Java

// Java code is not applicable for database queries.

C++

// C++ code is not applicable for database queries.

JavaScript

// JavaScript code is not applicable for database queries.

// C code is not applicable for database queries.

// C# code is not applicable for database queries.

This Python code snippet assumes you're working with an SQLite database directly. It runs a SQL query to find trips between the specified dates where neither client nor driver is banned, calculates the cancellation rate, and rounds it to two decimal places.

Complexity

Time Complexity: O(n), where n is the number of trips.
Space Complexity: O(1), as the result is a simple collection of days and rates.

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.

Python

import pandas as pd

# Sample data
trips_data = {
    'id': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    'client_id': [1, 2, 3, 4, 1, 2, 3, 2, 3, 4],
    'driver_id': [10, 11, 12, 13, 10, 11, 12, 12, 10, 13],
    'status': ['completed', 'cancelled_by_driver', 'completed', 'cancelled_by_client', 'completed', 'completed', 'completed', 'completed', 'completed', 'cancelled_by_driver'],
    'request_at': ['2013-10-01', '2013-10-01', '2013-10-01', '2013-10-01', '2013-10-02', '2013-10-02', '2013-10-02', '2013-10-03', '2013-10-03', '2013-10-03']
}
users_data = {
    'users_id': [1, 2, 3, 4, 10, 11, 12, 13],
    'banned': ['No', 'Yes', 'No', 'No', 'No', 'No', 'No', 'No'],
}

# Converting dictionaries to DataFrames
trips = pd.DataFrame(trips_data)
users = pd.DataFrame(users_data)

# Inner join on Trips and User, both client and driver shouldn't be banned
unbanned_trips = trips.merge(users[users['banned'] == 'No'], how='inner', left_on='client_id', right_on='users_id')
unbanned_trips = unbanned_trips.merge(users[users['banned'] == 'No'], how='inner', left_on='driver_id', right_on='users_id', suffixes=('_client', '_driver'))

# Compute cancellation rate
unbanned_trips['is_cancelled'] = unbanned_trips['status'].apply(lambda x: x != 'completed')
daily_cancellation_rate = unbanned_trips.groupby('request_at').apply(lambda x: round(x['is_cancelled'].sum() / x['is_cancelled'].count(), 2))

# Display the result
daily_cancellation_rate.reset_index(name='Cancellation Rate')

Java

// Java code is not applicable for database queries.

C++

// C++ code is not applicable for database queries.

JavaScript

// JavaScript code is not applicable for database queries.

// C code is not applicable for database queries.

// C# code is not applicable for database queries.

This solution in Python uses pandas to manipulate in-memory DataFrames (simulating database tables). It joins tables, filters out banned users, marks cancelled trips, and finally computes cancellation rates per day.

Complexity

Time Complexity: O(n), where n is the number of trips.
Space Complexity: O(n), as operations are conducted on the data frames built from the trips and users data.

Try this approach in the editor

Approach 3: Default Approach

MySQL

# Write your MySQL query statement below
SELECT
    request_at AS Day,
    ROUND(AVG(status != 'completed'), 2) AS 'Cancellation Rate'
FROM
    Trips AS t
    JOIN Users AS u1 ON (t.client_id = u1.users_id AND u1.banned = 'No')
    JOIN Users AS u2 ON (t.driver_id = u2.users_id AND u2.banned = 'No')
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at;

Try this approach in the editor

Complexity comparison

Approach	Complexity
Using SQL Join and Filtering	Time Complexity: O(n), where n is the number of trips. Space Complexity: O(1), as the result is a simple collection of days and rates.
Using Data Filtering and Aggregation	Time Complexity: O(n), where n is the number of trips. Space Complexity: O(n), as operations are conducted on the data frames built from the trips and users data.
Default Approach	–

Complexity analysis

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.

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.

Trips and Users solution and explanation

The problem

Approach overview

Approach 1: Using SQL Join and Filtering

Approach 2: Using Data Filtering and Aggregation

Approach 3: Default Approach

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Trips and Users solution and explanation

The problem

Approach overview

Approach 1: Using SQL Join and Filtering

Approach 2: Using Data Filtering and Aggregation

Approach 3: Default Approach

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems