Hopper Company Queries I - Practice Coding Problems

Hopper Company Queries I - Problem

Database Hard

🚗 Hopper Company Queries I - Monthly Business Analytics

You're a data analyst at Hopper, a ride-sharing company that's tracking their business growth throughout 2020. Your task is to generate a comprehensive monthly report that shows two key metrics:

Active Drivers: The cumulative number of drivers who have joined the company by the end of each month
Accepted Rides: The total number of rides that were successfully matched with drivers each month

You have access to three database tables:

Drivers Table: Contains driver information and their join dates
Rides Table: Contains all ride requests (both accepted and rejected)
AcceptedRides Table: Contains details only for rides that were successfully matched

The challenge is to handle the cumulative nature of driver counts (once a driver joins, they remain active) while calculating monthly ride statistics. You need to generate results for all 12 months of 2020, even if some months have zero activity.

Goal: Return a report ordered by month (1=January, 2=February, etc.) showing month, active_drivers, and accepted_rides for each month in 2020.

Input & Output

example_1.sql — Basic Example

$ Input: Drivers: [(1,'2019-01-01'), (2,'2020-03-21'), (3,'2020-07-31')] Rides: [(1,1,'2020-01-29'), (2,2,'2020-02-29'), (3,3,'2020-07-04')] AcceptedRides: [(1,1,10,30), (3,3,7,15)]

› Output: month=1, active_drivers=1, accepted_rides=1 month=2, active_drivers=1, accepted_rides=0 month=3, active_drivers=2, accepted_rides=0 ... month=7, active_drivers=3, accepted_rides=1 ...

💡 Note: Driver 1 joined before 2020, so active from month 1. Driver 2 joined in March, Driver 3 in July. Only rides 1 and 3 were accepted.

example_2.sql — No Activity Month

$ Input: Drivers: [(1,'2020-01-15')] Rides: [(1,1,'2020-03-10')] AcceptedRides: []

› Output: month=1, active_drivers=1, accepted_rides=0 month=2, active_drivers=1, accepted_rides=0 month=3, active_drivers=1, accepted_rides=0 ...

💡 Note: One driver joined in January, but no rides were accepted throughout 2020. Driver count remains 1 for all months.

example_3.sql — Edge Case Zero Drivers

$ Input: Drivers: [] Rides: [(1,1,'2020-05-01')] AcceptedRides: [(1,1,5,10)]

› Output: month=1, active_drivers=0, accepted_rides=0 month=2, active_drivers=0, accepted_rides=0 ... month=5, active_drivers=0, accepted_rides=1 ...

💡 Note: No drivers joined by 2020, so active_drivers is 0 for all months. However, ride was accepted (perhaps by drivers who joined after 2020).

Visualization

Tap to expand

Understanding the Visualization

Generate Calendar

Create a template with all 12 months to ensure no gaps in reporting

Calculate Cumulative Staff

Count how many drivers joined by each month-end (running total)

Aggregate Monthly Sales

Count accepted rides for each specific month

Merge Reports

Combine all data, filling zeros for inactive months

Key Takeaway

🎯 Key Insight: The secret is recognizing that driver counts are cumulative (running totals) while ride counts are periodic (monthly snapshots), requiring different aggregation strategies combined through efficient CTEs and LEFT JOINs.

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is drivers and m is rides. Each table is scanned once with efficient aggregation.

✓ Linear Growth

Space Complexity

O(12)

Space for 12 months plus intermediate aggregation results

✓ Linear Space

Constraints

1 ≤ Drivers.driver_id ≤ 10³
1980-01-01 ≤ Drivers.join_date ≤ 2020-12-31
1 ≤ Rides.ride_id ≤ 10⁵
1 ≤ Rides.user_id ≤ 10⁴
2020-01-01 ≤ Rides.requested_at ≤ 2020-12-31
AcceptedRides.ride_id is guaranteed to exist in Rides table
Report must include all 12 months of 2020, even with zero activity

Asked in

f Meta 45 a Amazon 38 ⊞ Microsoft 32 U Uber 28

The optimal solution uses Common Table Expressions (CTEs) to efficiently process the data in stages: first generate all 12 months, then calculate cumulative driver counts using window functions, aggregate monthly ride counts, and finally LEFT JOIN everything together to handle months with zero activity. This approach achieves O(n + m) complexity by scanning each table only once.

Common Approaches

Approach	Time	Space	Notes
✓ CTE with Window Functions (Optimal)	O(n + m)	O(12)	Use CTEs to generate month series and window functions for cumulative counts
Brute Force (Manual Month Iteration)	O(n×m)	O(1)	Manually query each month with subqueries for cumulative driver counts

CTE with Window Functions (Optimal) — Algorithm Steps

Create a CTE to generate all months 1-12 for 2020
Create another CTE to aggregate drivers by month with cumulative window function
Create a third CTE to count accepted rides by month
Join all CTEs together to get final result
Handle months with zero activity using LEFT JOINs and COALESCE

Visualization

Tap to expand

Step-by-Step Walkthrough

Generate Months

Create CTE with all 12 months of 2020

Cumulative Drivers

Use window function for running driver totals

Monthly Rides

Aggregate rides by month efficiently

Final Join

LEFT JOIN all CTEs to handle zero-activity months

Code -

solution.c — C

// Optimal CTE approach in C
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[2048];
    strcpy(query, 
        "WITH months AS ("
        "    SELECT 1 as month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL"
        "    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL"
        "    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL"
        "    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12"
        "), "
        "active_drivers AS ("
        "    SELECT m.month, COUNT(d.driver_id) as driver_count"
        "    FROM months m LEFT JOIN Drivers d "
        "    ON d.join_date <= LAST_DAY(STR_TO_DATE(CONCAT('2020-', LPAD(m.month, 2, '0'), '-01'), '%Y-%m-%d'))"
        "    GROUP BY m.month"
        "), "
        "ride_stats AS ("
        "    SELECT MONTH(r.requested_at) as month, COUNT(*) as ride_count"
        "    FROM Rides r JOIN AcceptedRides ar ON r.ride_id = ar.ride_id"
        "    WHERE YEAR(r.requested_at) = 2020 GROUP BY MONTH(r.requested_at)"
        ") "
        "SELECT m.month, COALESCE(ad.driver_count, 0) as active_drivers, "
        "COALESCE(rs.ride_count, 0) as accepted_rides "
        "FROM months m LEFT JOIN active_drivers ad ON m.month = ad.month "
        "LEFT JOIN ride_stats rs ON m.month = rs.month ORDER BY m.month");
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Where n is drivers and m is rides. Each table is scanned once with efficient aggregation.

✓ Linear Growth

Space Complexity

O(12)

Space for 12 months plus intermediate aggregation results

✓ Linear Space

Constraints

1 ≤ Drivers.driver_id ≤ 10³
1980-01-01 ≤ Drivers.join_date ≤ 2020-12-31
1 ≤ Rides.ride_id ≤ 10⁵
1 ≤ Rides.user_id ≤ 10⁴
2020-01-01 ≤ Rides.requested_at ≤ 2020-12-31
AcceptedRides.ride_id is guaranteed to exist in Rides table
Report must include all 12 months of 2020, even with zero activity

43.7K Views

High Frequency

~25 min Avg. Time

1.8K 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

🚗 Hopper Company Queries I - Monthly Business Analytics

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

CTE with Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler