The Daily Trips Chart
The ops dashboard at UrbanHop carries a bar per day showing how many bookings came in, and the shape of it is what the on-call team reads for demand spikes and dead days. Both outcomes count towards the bar: a booking that was cancelled was still demand arriving, and a day full of cancellations is exactly the kind of spike the chart exists to make visible.
The chart shows the opening stretch of the period — the seven earliest days on record — and reads left to right in date order. Days with no bookings at all have no bar.
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 day, with ride_date and how many bookings fall on that day as rides, counting every booking regardless of status. Earliest day first, keeping only the seven earliest days.
Example output
Shape only — these days and counts are invented:
| ride_date | rides |
|---|---|
| 2022-09-01 | 4 |
| 2022-09-02 | 2 |
| 2022-09-03 | 5 |
| 2022-09-04 | 3 |
| 2022-09-05 | 6 |
| 2022-09-06 | 1 |
| 2022-09-07 | 2 |
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