Late-Night Ride Requests for the May Safety Audit
The trust-and-safety team audits every ride requested in the small hours.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #303
Problem statement
The trust-and-safety team audits every ride requested in the small hours. A request is late-night when it was made between 00:00:00 and 04:59:59 (inclusive) on any day of May 2024.
Return the columns request_id, rider_id and requested_at of every late-night request in May 2024, ordered by requested_at ascending and then by request_id ascending.
Tables
Table: RideRequest
| Column | Type |
|---|---|
| request_id | int |
| rider_id | int |
| requested_at | datetime |
| pickup_zone | varchar |
Primary key: request_id.
One row per request a rider made in the app; requested_at is local time (IST).
Examples
Example 1
RideRequest
| request_id | rider_id | requested_at | pickup_zone |
|---|---|---|---|
| 1 | 701 | 2024-05-03 00:00:00 | Koramangala |
| 2 | 702 | 2024-05-03 04:59:59 | Hebbal |
| 3 | 703 | 2024-05-03 05:00:00 | Airport |
| 4 | 701 | 2024-05-12 02:15:00 | MG Road |
| 5 | 704 | 2024-04-30 03:30:00 | Whitefield |
| 6 | 705 | 2024-05-31 23:59:00 | Indiranagar |
| 7 | 706 | 2024-05-12 02:15:00 | Airport |
Output
| request_id | rider_id | requested_at |
|---|---|---|
| 1 | 701 | 2024-05-03 00:00:00 |
| 2 | 702 | 2024-05-03 04:59:59 |
| 4 | 701 | 2024-05-12 02:15:00 |
| 7 | 706 | 2024-05-12 02:15:00 |
How to solve Late-Night Ride Requests for the May Safety Audit
The window has two parts that must both hold. The date part is May 2024, best written as the half-open range requested_at >= '2024-05-01' and < '2024-06-01', which keeps every second of 31 May and nothing of April 30 or June 1. The time-of-day part is 00:00:00 to 04:59:59, which is exactly the hours 0, 1, 2, 3 and 4 — so HOUR(requested_at) < 5 (or BETWEEN 0 AND 4) captures it without worrying about minutes and seconds.
Checking the boundaries is the whole exercise: 00:00:00 is in (hour 0), 04:59:59 is in (hour 4), 05:00:00 is out (hour 5). Comparing the formatted time DATE_FORMAT(requested_at, '%H:%i:%s') <= '04:59:59' works too, because the fixed-width text sorts like time.
Two requests can share a timestamp, so request_id breaks the tie in the required order. The date range can use an index on requested_at; the hour test is then applied to the month's rows.
Reference solution (MySQL)
SELECT request_id, rider_id, requested_at
FROM RideRequest
WHERE requested_at >= '2024-05-01' AND requested_at < '2024-06-01'
AND HOUR(requested_at) < 5
ORDER BY requested_at, request_idAnother way
SELECT request_id, rider_id, requested_at FROM RideRequest WHERE DATE_FORMAT(requested_at, '%Y-%m') = '2024-05' AND HOUR(requested_at) BETWEEN 0 AND 4 ORDER BY requested_at, request_idAnother way
SELECT request_id, rider_id, requested_at FROM RideRequest WHERE YEAR(requested_at) = 2024 AND MONTH(requested_at) = 5 AND DATE_FORMAT(requested_at, '%H:%i:%s') <= '04:59:59' ORDER BY requested_at ASC, request_id ASC← Ride Request Outcome Mix: Rider, Driver or No Driver · Undelivered Shipments Past Their Promised Date →