Trips and Users - Problem

You are given two tables: Trips and Users.

The Trips table contains information about taxi trips with columns:

  • id (primary key): unique trip identifier
  • client_id: foreign key to users table
  • driver_id: foreign key to users table
  • city_id: city identifier
  • status: trip status ('completed', 'cancelled_by_driver', 'cancelled_by_client')
  • request_at: trip request date

The Users table contains user information with columns:

  • users_id (primary key): unique user identifier
  • banned: ban status ('Yes', 'No')
  • role: user role ('client', 'driver', 'partner')

Calculate the cancellation rate for each day between '2013-10-01' and '2013-10-03'. The cancellation rate is computed by dividing the number of canceled requests (by client or driver) with unbanned users by the total number of requests with unbanned users on that day.

Return the result with the cancellation rate rounded to 2 decimal places.

Table Schema

Trips
Column Name Type Description
id PK int Primary key - unique trip identifier
client_id int Foreign key to Users table - client user ID
driver_id int Foreign key to Users table - driver user ID
city_id int City identifier
status enum Trip status: 'completed', 'cancelled_by_driver', 'cancelled_by_client'
request_at varchar Trip request date in YYYY-MM-DD format
Primary Key: id
Users
Column Name Type Description
users_id PK int Primary key - unique user identifier
banned enum Ban status: 'Yes' or 'No'
role enum User role: 'client', 'driver', 'partner'
Primary Key: users_id

Input & Output

Example 1 — Mixed Trip Status
Input Tables:
Trips
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 1 completed 2013-10-02
Users
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
💡 Note:

On 2013-10-01, trips with IDs 1, 3, 4 have unbanned users (trip 2 excluded due to banned client). Out of 3 valid trips, 1 was cancelled (trip 4), so rate = 1/3 = 0.33. On 2013-10-02, all 2 valid trips were completed, so rate = 0/2 = 0.00.
Example 2 — All Cancelled Day
Input Tables:
Trips
id client_id driver_id city_id status request_at
8 1 10 1 cancelled_by_driver 2013-10-03
9 3 12 1 cancelled_by_client 2013-10-03
Users
users_id banned role
1 No client
3 No client
10 No driver
12 No driver
Output:
Day Cancellation Rate
2013-10-03 1.00
💡 Note:

On 2013-10-03, both trips were cancelled and all users are unbanned. Cancellation rate = 2/2 = 1.00 (100% cancellation rate).

Constraints

  • 1 ≤ id, client_id, driver_id, city_id, users_id ≤ 1000
  • status is either 'completed', 'cancelled_by_driver', or 'cancelled_by_client'
  • banned is either 'Yes' or 'No'
  • role is either 'client', 'driver', or 'partner'
  • request_at is a valid date in format 'YYYY-MM-DD'

Visualization

Tap to expand
Trips and Users: Cancellation Rate CalculationInput Tablesidstatusrequest_atclient1cancelled10-01unbanned2completed10-01unbanned3completed10-02unbannedJOIN & AGGREGATEGROUP BY dateOutput: Cancellation RatesDayRate2013-10-010.502013-10-020.00Formula: Cancellation Rate = (Cancelled Trips) / (Total Trips) per dayCASE WHEN status LIKE 'cancelled%'THEN 1.0 ELSE 0.0 ENDKey: Filter unbanned users first, then calculate ratios by date
Understanding the Visualization
1
Join Tables
Connect Trips with Users for both client and driver
2
Filter Data
Keep only unbanned users within date range
3
Calculate Ratio
Group by date and compute cancellation rates
Key Takeaway
🎯 Key Insight: Use multiple JOINs to enforce data integrity constraints, then GROUP BY with conditional aggregation to calculate business metrics

Related Problems

Asked in
Uber 15 Lyft 8
68.0K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen