Slow request-to-accept trips

SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 20 minutes. Part of the Pro drill bank.

A ride app records an event when a trip is requested and another when it is accepted by a driver. Both events share the same trip_id, and they sit in the same table, trip_events, as two separate rows. A trip is slow when the accepted time is more than 90 seconds after the requested time. Trips that were never accepted are ignored. Count the slow trips for each city and for each hour of the request time, then return only the one busiest bucket. The hour is the request time cut down to the start of the hour (for example 09:10 becomes 09:00). Return city, hour_bucket (a timestamp) and slow_trips. There is exactly one busiest bucket, so return 1 row.

Requirements

  • Match each requested event with the accepted event of the same trip.
  • Keep trips where the gap is more than 90 seconds.
  • Group by city and by the hour of the request time.
  • Return only the bucket with the most slow trips: city, hour_bucket, slow_trips.

Constraints

  • Each trip has at most one requested and one accepted event.
  • Times are timestamps with second precision.
  • event_type is either requested or accepted.

Examples

Input: trip_events trip_id | city | event_type | event_ts 1 | Pune | requested | 2024-07-01 09:10:00 1 | Pune | accepted | 2024-07-01 09:12:00 2 | Pune | requested | 2024-07-01 09:20:00 2 | Pune | accepted | 2024-07-01 09:23:20 3 | Pune | requested | 2024-07-01 09:30:00 3 | Pune | accepted | 2024-07-01 09:31:00 4 | Pune | requested | 2024-07-01 10:05:00 4 | Pune | accepted | 2024-07-01 10:06:40 5 | Oslo | requested | 2024-07-01 09:15:00 5 | Oslo | accepted | 2024-07-01 09:17:30 6 | Oslo | requested | 2024-07-01 09:40:00 6 | Oslo | accepted | 2024-07-01 09:41:30 ... Output: city | hour_bucket | slow_trips Pune | 2024-07-01 09:00:00 | 3 Why this passes: Pune 09:00 has three slow trips (1, 2 and 10); Oslo 09:00 has two (5 and 9). Trip 3 (60 s) and trip 6 (exactly 90 s) are not slow, and trip 8 was never accepted.

Input: trip_events trip_id | city | event_type | event_ts 1 | Pune | requested | 2024-07-01 09:00:00 1 | Pune | accepted | 2024-07-01 09:03:00 Output: city | hour_bucket | slow_trips Pune | 2024-07-01 09:00:00 | 1 Why this passes: One slow trip is enough to win when it is the only one.

Topics: lakebench, sql, events, pairing, time gap.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Slow request-to-accept trips

Interview-style drill: Pair each trip's request with its acceptance, flag gaps over 90 seconds and find the busiest city and hour.

`trip_events` records a `requested` event and, for trips that were picked up, an `accepted` event for the same `trip_id`. A trip is **slow** when its accepted event is **more than 90 seconds** after its requested event. Trips never accepted are ignored. Count slow trips per city and per hour of the request time. Return only the single busiest bucket. Columns: `city`, `hour_bucket` (the request time truncated to the hour, a TIMESTAMP), `slow_trips`. There is exactly one busiest bucket.