Trips and Users - Practice Coding Problems

Trips and Users - Problem

Database Hard

Trips and Users - Calculate Daily Cancellation Rates for Taxi Service

You're working for a taxi service company that needs to analyze their trip cancellation rates. You have two database tables:

Trips Table: Contains information about taxi trips
• id: Unique trip identifier
• client_id: Reference to the customer who requested the trip
• driver_id: Reference to the driver assigned to the trip
• status: Trip outcome ('completed', 'cancelled_by_driver', 'cancelled_by_client')
• request_at: Date when the trip was requested

Users Table: Contains information about all users (clients and drivers)
• users_id: Unique user identifier
• role: User type ('client', 'driver', 'partner')
• banned: Whether the user is banned ('Yes', 'No')

Your Mission: Calculate the daily cancellation rate for trips between 2013-10-01 and 2013-10-03, but only consider trips where both the client and driver are not banned. The cancellation rate is the percentage of cancelled trips (by either client or driver) out of total valid trips for each day. Round the result to 2 decimal places.

Formula: Cancellation Rate = (Cancelled Trips with Unbanned Users) / (Total Trips with Unbanned Users)

Input & Output

example_1.sql — Basic scenario with mixed cancellations

$ Input: Trips: [(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')] Users: [(1,'No','client'), (2,'Yes','client'), (3,'No','client'), (4,'No','client'), (10,'No','driver'), (11,'No','driver'), (12,'No','driver'), (13,'Yes','driver')]

› Output: Day: 2013-10-01, Cancellation Rate: 0.33

💡 Note: On 2013-10-01: Trip 1 (completed, valid), Trip 2 (cancelled but client banned - excluded), Trip 3 (completed, valid), Trip 4 (cancelled but driver banned - excluded). Valid trips: 2, Cancelled valid trips: 0, Rate: 0/2 = 0.00. Wait, let me recalculate: Only trips with unbanned users count, so we have Trip 1 (client 1 + driver 10, both unbanned, completed) and Trip 3 (client 3 + driver 12, both unbanned, completed). That's 2 valid trips, 0 cancelled = 0.00 rate.

example_2.sql — Day with only completed trips

$ Input: Trips: [(5,1,10,1,'completed','2013-10-02'), (6,2,11,6,'completed','2013-10-02')] Users: [(1,'No','client'), (2,'Yes','client'), (10,'No','driver'), (11,'No','driver')]

› Output: Day: 2013-10-02, Cancellation Rate: 0.00

💡 Note: On 2013-10-02: Trip 5 (client 1 unbanned + driver 10 unbanned = valid, completed), Trip 6 (client 2 banned - excluded). Only 1 valid trip, 0 cancellations = 0.00 rate.

example_3.sql — Day with no valid trips (edge case)

$ Input: Trips: [(7,2,13,6,'cancelled_by_client','2013-10-03')] Users: [(2,'Yes','client'), (13,'Yes','driver')]

› Output: No result for 2013-10-03

💡 Note: On 2013-10-03: Trip 7 has both client 2 and driver 13 banned, so it's excluded. No valid trips means no result row for this date.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is trips count and m is users count. Database can optimize JOINs and filtering efficiently

✓ Linear Growth

Space Complexity

O(1)

Only storing aggregated results, not individual records

✓ Linear Space

Constraints

1 ≤ Number of trips ≤ 10⁵
1 ≤ Number of users ≤ 10⁴
Date range is fixed: 2013-10-01 to 2013-10-03
Status values are only: 'completed', 'cancelled_by_driver', 'cancelled_by_client'
Banned values are only: 'Yes', 'No'
Role values are: 'client', 'driver', 'partner'
Both client AND driver must be unbanned for a trip to be considered

Asked in

The optimal solution uses a single SQL query with INNER JOINs to filter trips where both client and driver are unbanned. Key techniques: conditional aggregation with CASE WHEN to count cancelled trips, GROUP BY date for daily analysis, and ROUND function for precise percentage calculation. Time complexity: O(n + m) where n = trips, m = users.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Separate Queries)	O(n²)	O(1)	Execute separate queries for each day and user lookup
Optimized SQL with JOINs (Optimal)	O(n + m)	O(1)	Use SQL JOINs and conditional aggregation to solve in one efficient query

Brute Force (Multiple Separate Queries) — Algorithm Steps

Query all trips for each date separately
For each trip, execute separate queries to check if client is banned
For each trip, execute separate queries to check if driver is banned
Filter out trips with banned users
Count total valid trips and cancelled trips for each date
Calculate cancellation rate and round to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Query All Trips

Get all trips for each date separately

Check Each Client

For each trip, query if client is banned

Check Each Driver

For each trip, query if driver is banned

Filter Valid Trips

Keep only trips with unbanned users

Calculate Rate

Count cancellations and compute percentage

Code -

solution.c — C

// Brute Force Approach - Multiple Queries
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <math.h>

typedef struct {
    int id, client_id, driver_id, city_id;
    char status[50];
    char request_at[20];
} Trip;

typedef struct {
    int users_id;
    char banned[10];
    char role[20];
} User;

typedef struct {
    char date[20];
    double rate;
} Result;

User* findUser(User* users, int userCount, int userId) {
    for (int i = 0; i < userCount; i++) {
        if (users[i].users_id == userId) {
            return &users[i];
        }
    }
    return NULL;
}

Result* getCancellationRates(Trip* trips, int tripCount, User* users, int userCount, int* resultCount) {
    Result* results = malloc(3 * sizeof(Result));
    *resultCount = 0;
    
    char dates[3][20] = {"2013-10-01", "2013-10-02", "2013-10-03"};
    
    for (int d = 0; d < 3; d++) {
        int validTripCount = 0;
        int cancelledCount = 0;
        
        // Check each trip for this date
        for (int i = 0; i < tripCount; i++) {
            if (strcmp(trips[i].request_at, dates[d]) != 0) continue;
            
            // Check if client is banned
            User* client = findUser(users, userCount, trips[i].client_id);
            if (client && strcmp(client->banned, "Yes") == 0) continue;
            
            // Check if driver is banned
            User* driver = findUser(users, userCount, trips[i].driver_id);
            if (driver && strcmp(driver->banned, "Yes") == 0) continue;
            
            validTripCount++;
            
            if (strcmp(trips[i].status, "cancelled_by_client") == 0 ||
                strcmp(trips[i].status, "cancelled_by_driver") == 0) {
                cancelledCount++;
            }
        }
        
        if (validTripCount > 0) {
            strcpy(results[*resultCount].date, dates[d]);
            results[*resultCount].rate = round((double)cancelledCount / validTripCount * 100.0) / 100.0;
            (*resultCount)++;
        }
    }
    
    return results;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each trip (n), we make additional queries to check users, resulting in n² database operations

⚠ Quadratic Growth

Space Complexity

O(1)

Only storing temporary results for each calculation

✓ Linear Space

Constraints

1 ≤ Number of trips ≤ 10⁵
1 ≤ Number of users ≤ 10⁴
Date range is fixed: 2013-10-01 to 2013-10-03
Status values are only: 'completed', 'cancelled_by_driver', 'cancelled_by_client'
Banned values are only: 'Yes', 'No'
Role values are: 'client', 'driver', 'partner'
Both client AND driver must be unbanned for a trip to be considered

25.0K Views

Medium Frequency

~15 min Avg. Time

850 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

Input & Output

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Separate Queries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler