SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 20 minutes. Part of the Pro drill bank.
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.
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
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.