Fare per Kilometer by Vehicle Type
Pricing wants to know how much riders pay per kilometer on each vehicle type. Use completed trips and compute total fare divided by total distance.
Expected output
vehicle_type, trips, fare_per_km (rounded to 2 decimals). Sorted by fare_per_km descending.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: Ride-share marketplace
Trip requests in four US cities from January to March 2026, with drivers, riders, fares and ratings.
drivers
One row per driver. A driver only serves their own city.
| driver_id | INTEGER | primary key |
| name | TEXT | |
| city | TEXT | |
| joined_date | TEXT | YYYY-MM-DD |
| vehicle_type | TEXT | standard, comfort or xl |
riders
One row per rider account.
| rider_id | INTEGER | primary key |
| name | TEXT | |
| city | TEXT | |
| signup_date | TEXT | YYYY-MM-DD |
trips
One row per trip request, whatever its outcome.
| trip_id | INTEGER | primary key, increases with requested_at |
| rider_id | INTEGER | |
| driver_id | INTEGER | NULL when no driver was assigned |
| city | TEXT | |
| requested_at | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
| dropoff_at | TEXT | NULL unless completed |
| status | TEXT | completed, cancelled_rider, cancelled_driver or no_driver |
| distance_km | REAL | estimated route distance |
| fare | REAL | USD, NULL unless completed |
| rider_rating | INTEGER | 1-5 stars given by the rider, NULL if not rated |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Monthly Revenue Trend (easy)
- Average Order Value by Country (easy)
- Repeat Purchase Rate (medium)