Fare per kilometer by city
mediumFare per kilometer by city
Uber SQL Interview Question
Uber's pricing team compares cities by how much riders pay for each kilometer traveled.
For completed trips, find each city and its fare per kilometer as fare_per_km: the city's total fare divided by its total distance, rounded to 2 decimal places. Do not average the per-trip values. Sort the rows by fare_per_km, highest first.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Data Scientist
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
| city | fare_per_km |
|---|---|
| Seattle | 2.66 |
| Chicago | 2.51 |
| Austin | 1.93 |
Explanation
Chicago's completed trips earned 53.00 in fares over 21.1 kilometers, which is 2.51 per kilometer. Averaging each trip's own rate would give a different number, because short trips would count as much as long ones. Canceled trips have no fare or distance, so they are not included.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Uber
Difficulty
medium
Topic
calculations
Your query
Starting the SQL engine...