Late-Night Ride Requests for the May Safety Audit

Easy
303.

Late-Night Ride Requests for the May Safety Audit

EasyDatesBasics

MySQL

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

ColumnType
request_idint
rider_idint
requested_atdatetime
pickup_zonevarchar

Primary key: request_id.

One row per request a rider made in the app; requested_at is local time (IST).

Example

The rows must come back in the order shown.

RideRequest

request_idrider_idrequested_atpickup_zone
17012024-05-03 00:00:00Koramangala
27022024-05-03 04:59:59Hebbal
37032024-05-03 05:00:00Airport
47012024-05-12 02:15:00MG Road
57042024-04-30 03:30:00Whitefield
67052024-05-31 23:59:00Indiranagar
77062024-05-12 02:15:00Airport

Output

request_idrider_idrequested_at
17012024-05-03 00:00:00
27022024-05-03 04:59:59
47012024-05-12 02:15:00
77062024-05-12 02:15:00
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
request_idint
rider_idint
requested_atdatetime
pickup_zonevarchar

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_idrider_idrequested_atpickup_zone
17012024-05-03 00:00:00Koramangala
27022024-05-03 04:59:59Hebbal
37032024-05-03 05:00:00Airport
47012024-05-12 02:15:00MG Road
57042024-04-30 03:30:00Whitefield
67052024-05-31 23:59:00Indiranagar
77062024-05-12 02:15:00Airport

Output

request_idrider_idrequested_at
17012024-05-03 00:00:00
27022024-05-03 04:59:59
47012024-05-12 02:15:00
77062024-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_id

Another 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_id

Another 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 →