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 INPUT DATA Trips Table id|client|driver|status|date 1 | 1 | 10 | completed | 10-01 2 | 2 | 11 | cancelled | 10-01 3 | 3 | 12 | completed | 10-01 4 | 4 | 13 | cancelled | 10-01 5 | 1 | 10 | completed | 10-02 6 | 2 | 11 | completed | 10-02 Users Table users_id | banned | role 1 | No | client 2 | Yes | client 10 | No | driver 11 | No | driver Date Range: 2013-10-01 to 10-03 Banned user 2 excluded ALGORITHM STEPS 1 JOIN Tables Link Trips with Users 2 Filter Unbanned WHERE banned='No' 3 Group by Date GROUP BY request_at 4 Calculate Rate cancelled/total ROUND 2 Cancellation Rate Formula: SUM(cancelled) / COUNT(*) ROUND(result, 2) SELECT request_at AS Day, ROUND(SUM(CASE WHEN status!='completed'...),2) FINAL RESULT Day Rate 2013-10-01 0.33 2013-10-02 0.00 2013-10-03 0.50 Calculation Example 10-01: 1 cancel / 3 trips = 0.333... = 0.33 Excluded: banned user OK - Query Complete 3 rows returned Key Insight: Use LEFT JOIN to connect Trips with Users twice (client and driver). Filter WHERE both client.banned='No' AND driver.banned='No'. Use CASE WHEN to count cancelled trips, then divide by total COUNT(*) and ROUND to 2 decimal places for each date. TutorialsPoint - Trips and Users | Optimal Solution

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