The Earnings Statement
UrbanHop pays its drivers weekly, and the payout run is built straight from the bookings table. A driver is paid for the fares of the trips they actually delivered — a cancelled booking carries a price, since the price is fixed when the ride is booked, but no money changed hands and paying on it would be paying for nothing.
The statement goes out under the driver's name, and finance takes the figure to two decimal places. A driver with no completed trips has no statement and does not appear.
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 |
drivers — one row per driver on the platform. rating is their running star rating out of 5, and city is the city they drive in.
| id | name | city | rating |
|---|---|---|---|
| 1 | Sam Rios | Austin | 4.9 |
| 2 | Priya Desai | Denver | 4.6 |
| 3 | Oscar Lund | Seattle | 4.8 |
| 4 | Nina Cole | Austin | 4.2 |
Both tables already exist 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 their name and their earnings — the total of the fares of the trips they delivered, rounded to two decimal places — as earnings. Highest earning first; drivers on the same figure are separated alphabetically by name.
Example output
Shape only — these drivers and figures are invented. A round amount comes back plain, as 96 rather than 96.00:
| name | earnings |
|---|---|
| Dana Fox | 210.5 |
| Rosa Vega | 96 |
| Milo Reyes | 88.25 |
| Tariq Bell | 40.75 |
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