Hopper Company Queries I

Hopper Company Queries I

What is this problem about?

The Hopper Company Queries I interview question is a complex SQL challenge involving a ride-sharing service. You are given tables for Drivers, Rides, and AcceptedRides. You need to produce a report for each month of the year 2020 showing:

1. The number of active drivers (drivers who joined before or during that month).
2. The number of accepted rides that occurred in that month. This requires careful joining of temporal data and cumulative counting across a 12-month period.

Why is this asked in interviews?

Uber and similar logistics companies use this "Hard" database question to test a candidate's ability to perform complex analytical queries. It evaluates your skill in creating a "Time Series" in SQL (generating months), performing multiple LEFT JOIN operations to ensure all months are present, and handling cumulative aggregations. It’s a high-level test of reporting logic and data modeling.

Algorithmic pattern used

This problem follows the Time Series Generation and Aggregation pattern.

1. Generate Months: Create a CTE or temporary table containing the numbers 1 through 12 to represent each month of 2020.
2. Cumulative Drivers: Use a subquery or window function to count drivers whose join date is $\le$ the end of each month.
3. Ride Counts: Join with the Rides and AcceptedRides tables to count rides that occurred in each month.
4. Join: Use a LEFT JOIN from the "Months" table to the aggregated data to ensure months with zero drivers or zero rides are included.

Example explanation

• If 5 drivers joined in Jan 2020 and 2 in Feb 2020, the active drivers for Feb would be 7.
• If there were 10 rides in Jan and 0 in Feb, the report for Feb should show "7 active drivers, 0 accepted rides".

Common mistakes candidates make

• Missing Months: Using a simple join that excludes months with no ride activity.
• Non-Cumulative Counts: Reporting only the drivers who joined in that specific month instead of the total active fleet.
• Date Filtering: Forgetting to filter drivers and rides specifically for the year 2020 or earlier.

Interview preparation tip

When asked to report data by month, always start by ensuring you have a "base" table of months. This prevents "gaps" in your data. Practice the WITH RECURSIVE syntax or a simple union of numbers to generate sequences in SQL.