Market Share by Driver
Concentration is a risk UrbanHop takes seriously: if one driver is carrying most of a city's revenue, that city is one resignation away from a bad quarter. The board asks for the split — what proportion of everything the platform earned went through each driver — rather than the raw totals, because a proportion is comparable across cities and across weeks and a dollar figure is not.
Only completed trips earned anything, so both the driver's figure and the platform total are over completed bookings alone. Drivers are identified by number here, since this goes into a risk model rather than onto a wall.
rides — one row per booking. rider_id points at a row in riders and driver_id at a row in drivers. distance_km is the trip length in kilometres and fare the price in dollars; both are recorded when the booking is made, so a booking that never happened still carries them. status is either completed or cancelled. ride_date is the day the ride was booked for.
| id | rider_id | driver_id | distance_km | fare | ride_date | status |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 5.2 | 12.5 | 2023-06-01 | completed |
| 2 | 1 | 4 | 3.0 | 8.0 | 2023-06-03 | completed |
| 3 | 2 | 2 | 10.0 | 22.0 | 2023-06-02 | completed |
| 4 | 3 | 1 | 2.1 | 6.5 | 2023-06-05 | cancelled |
| 5 | 3 | 4 | 4.4 | 11.0 | 2023-06-06 | completed |
| 6 | 4 | 3 | 7.8 | 18.0 | 2023-06-04 | completed |
| 7 | 5 | 2 | 1.5 | 5.0 | 2023-06-07 | cancelled |
| 8 | 2 | 2 | 12.3 | 26.5 | 2023-06-10 | completed |
| 9 | 1 | 1 | 6.6 | 15.0 | 2023-06-12 | completed |
| 10 | 4 | 3 | 3.3 | 9.0 | 2023-06-15 | completed |
| 11 | 5 | 2 | 8.8 | 19.5 | 2023-06-18 | completed |
| 12 | 3 | 4 | 2.9 | 7.5 | 2023-06-20 | cancelled |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row per driver with at least one completed trip, with driver_id and their share of all completed fares as a percentage, rounded to one decimal place, as share_pct. Largest share first; drivers on the same share are separated by driver_id, smallest first.
Example output
Shape only — these drivers and shares are invented. A whole percentage comes back as 16, not 16.0:
| driver_id | share_pct |
|---|---|
| 7 | 52.6 |
| 9 | 21.4 |
| 12 | 16 |
| 15 | 10 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in