Completed trips by hour of day
mediumCompleted trips by hour of day
Uber SQL Interview Question
Uber's marketplace team wants to know which hours of the day are busiest, so it can encourage more drivers to be online at those times.
For each hour of the day (0 to 23), count the completed trips that were requested in that hour. Show the hour as hour_of_day and the count as completed_trips, and leave out hours with no completed trips. Sort the rows by hour_of_day, earliest first.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- Data Engineer
- Analytics Engineer
tripsTable14 rows
| Column Name | Type |
|---|---|
| trip_id | BIGINT |
| rider_id | BIGINT |
| driver_id | BIGINT |
| city | VARCHAR |
| requested_at | TIMESTAMP |
| status | VARCHAR |
| distance_km | DOUBLE |
| fare | DOUBLE |
tripsExample Input
| trip_id | rider_id | driver_id | city | requested_at | status | distance_km | fare |
|---|---|---|---|---|---|---|---|
| 9001 | 301 | 401 | Chicago | 2024-05-03 07:42:00 | completed | 8.4 | 18.9 |
| 9002 | 302 | 402 | Chicago | 2024-05-03 08:15:00 | completed | 3.1 | 9.5 |
| 9003 | 303 | 403 | Austin | 2024-05-03 08:47:00 | rider_canceled | 0 | 0 |
| 9004 | 304 | 404 | Austin | 2024-05-03 12:05:00 | completed | 12.6 | 24.3 |
| 9005 | 305 | 401 | Chicago | 2024-05-03 17:30:00 | completed | 5.2 | 13.4 |
| 9006 | 306 | 405 | Seattle | 2024-05-03 17:55:00 | driver_canceled | 0 | 0 |
| 9007 | 307 | 406 | Seattle | 2024-05-03 18:10:00 | completed | 9.8 | 26.1 |
| 9008 | 308 | 402 | Chicago | 2024-05-03 18:25:00 | completed | 4.4 | 11.2 |
Example Output
| hour_of_day | completed_trips |
|---|---|
| 7 | 1 |
| 8 | 1 |
| 12 | 1 |
| 17 | 1 |
| 18 | 2 |
Explanation
Trips requested at 6:10 p.m. and 6:25 p.m. both fall in hour 18, so that hour has 2 completed trips. Trip 9003 was requested at 8:47 a.m., but the rider canceled it, so it does not add to hour 8. Every other hour in the example has 1 completed trip.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Uber
Difficulty
medium
Topic
dates
Your query
Starting the SQL engine...