Hopper Company Queries II

Hopper Company Queries II

What is this problem about?

Building on the Hopper series, the Hopper Company Queries II coding problem asks for the "working percentage" of drivers for each month of 2020. The working percentage is defined as the number of drivers who accepted at least one ride in a month divided by the total number of active drivers (drivers who joined on or before that month). The result should be rounded to two decimal places.

Why is this asked in interviews?

Companies like Uber ask this to evaluate a candidate's ability to handle multi-layered aggregations and arithmetic in SQL. It tests whether you can maintain context across different groupings (e.g., total drivers joined vs. drivers active in rides) and handle potential "division by zero" errors or NULL values gracefully.

Algorithmic pattern used

The pattern involves Relational Aggregation and Ratio Calculation:

1. Generate a 12-month sequence.
2. Calculate the Active Driver Count for each month (cumulative joiners).
3. Calculate the Working Driver Count for each month (drivers present in the AcceptedRides table for that month).
4. Join these on the month sequence.
5. Compute the ratio: WorkingDrivers / ActiveDrivers * 100. Use IFNULL or COALESCE to handle months with no activity.

Example explanation

• In March 2020, you have 100 total drivers who have joined.
• Out of those 100, only 40 actually accepted a ride in March.
• Result for March: $40 / 100 imes 100 = 40.00\%$.

Common mistakes candidates make

• Filtering drivers too early: Excluding drivers who haven't accepted a ride from the "total active" count.
• Null Handling: Returning NULL instead of 0.00 for months with no active drivers or rides.
• Rounding errors: Not using the ROUND(..., 2) function as specified.

Interview preparation tip

Always consider the "denominator." In ratio problems, defining exactly what constitutes the total population (the active drivers) versus the sub-population (working drivers) is the key to accuracy.