Bookings, Running Total
The growth chart on the wall at UrbanHop is not daily takings, it is the line that only goes up: every completed booking's fare added to everything banked before it. Investors read the slope of that line, and a flat stretch in it is a bad week nobody can argue away.
Each point on the line is one completed booking, labelled with the day it happened, and carries the running total up to and including that booking. Cancelled bookings put nothing in the bank and are not on the chart. The wall display shows only the opening stretch — the first five points — and money is shown to two decimal places.
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 completed booking, with ride_date and the running total of fares up to and including that booking, rounded to two decimal places, as cumulative_fares. Build the running total by day, using the booking id to settle two bookings on the same day, and return the rows in that same sequence, keeping only the first five.
Example output
Shape only — these days and figures are invented:
| ride_date | cumulative_fares |
|---|---|
| 2022-09-01 | 18.25 |
| 2022-09-02 | 44 |
| 2022-09-03 | 71.5 |
| 2022-09-05 | 96.75 |
| 2022-09-06 | 130 |
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