Driver Trip Distance
ASKED IN INTERVIEWUber
Problem Statement
Uber operations teams are analyzing driver efficiency and trip patterns. To understand driver engagement, we need to calculate the average distance traveled per driver.
Write an SQL query to find the average distance for each driver, but only for trips that have been successfully completed.
Rules:
- Only include trips where the status is completed.
- Trips with statuses like cancelled by driver or cancelled by client must be excluded.
- For drivers who have zero completed trips, they should not appear in the final output.
- The average distance should be rounded to 2 decimal places.
- Output columns: driver_name, avg_distance.
- Results must be sorted by avg_distance in descending order.
Tests your understanding of
Filtering, Aggregation and Rounding
Input Tables
Drivers
| driver_id(INTEGER) | driver_name(VARCHAR) | join_date(DATE) |
|---|---|---|
| 1 | Michael | 2025-01-01 |
| 2 | Sarah | 2025-01-05 |
| 3 | Robert | 2025-01-10 |
| 4 | Daniel | 2025-01-15 |
| 5 | Jessica | 2025-02-01 |
| 6 | David | 2025-02-05 |
Trips
| trip_id(INTEGER) | driver_id(INTEGER) | distance(DECIMAL) | status(VARCHAR) |
|---|---|---|---|
| 101 | 1 | 12.5 | completed |
| 102 | 1 | 8 | completed |
| 103 | 2 | 15.2 | completed |
| 104 | 2 | 5 | cancelled_by_driver |
| 105 | 3 | 20 | completed |
| 106 | 1 | 10 | completed |
Vehicles
| vehicle_id(INTEGER) | driver_id(INTEGER) | make(VARCHAR) |
|---|---|---|
| 501 | 1 | Toyota |
| 502 | 2 | Honda |
| 503 | 3 | Tesla |
| 504 | 4 | Ford |
| 505 | 5 | Nissan |
| 506 | 6 | Hyundai |
Expected Output
| driver_name(VARCHAR) | avg_distance(DECIMAL) |
|---|---|
| Robert | 20 |
| Sarah | 15.2 |
| Michael | 10.17 |
Tags
MediumASKED IN INTERVIEWFilteringAggregationRounding
10-15 min
68%