How Much Revenue Did Completed Rides Bring In?
UrbanHop's finance team is closing the month and needs the revenue figure for the period: the money the company actually took, as one number.
The trip table is not a revenue table. It records every booking, including the ones that never happened — a cancelled trip still carries a fare, because the fare was quoted when the trip was booked, and that quoted amount was never charged to anybody. Adding up the whole column would overstate the month by every cancellation in it.
rides — one row per trip booked. status is completed or cancelled. fare is the quoted fare in dollars, present on cancelled trips as well as completed ones.
| 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 | 8 | 2023-06-03 | completed |
| 3 | 2 | 2 | 10 | 22 | 2023-06-02 | completed |
| 4 | 3 | 1 | 2.1 | 6.5 | 2023-06-05 | cancelled |
| 5 | 3 | 4 | 4.4 | 11 | 2023-06-06 | completed |
| 6 | 4 | 3 | 7.8 | 18 | 2023-06-04 | completed |
| 7 | 5 | 2 | 1.5 | 5 | 2023-06-07 | cancelled |
| 8 | 2 | 2 | 12.3 | 26.5 | 2023-06-10 | completed |
| 9 | 1 | 1 | 6.6 | 15 | 2023-06-12 | completed |
| 10 | 4 | 3 | 3.3 | 9 | 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 returning a single row with a single column, total_fare, holding the total fare across the trips whose status is completed. Cancelled trips contribute nothing.
Example output — shape only. If the completed trips had brought in two hundred and sixty-eight dollars and seventy-five cents, the single row would read:
| total_fare |
|---|
| 268.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