SQL Trips with Safety Interventions | Dataford Interview Questions

Your question is SQL Trips with Safety Interventions. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Waymo analyzes autonomous vehicle trip records to identify sequences that may require operational review. Write a PostgreSQL query to find consecutive trips for the same vehicle where both trips recorded at least one safety intervention and the gap between them was no more than 30 minutes.

Requirements

Count only interventions with intervention_type = 'safety_intervention' during each trip.
Compare each trip with the immediately preceding trip for the same vehicle using a window function.
Return adjacent trip pairs whose preceding and current trips both have safety interventions and whose gap is between 0 and 30 minutes, inclusive.
Sort by vehicle_id and the current trip's start time.

Schema

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
vehicle_id	VARCHAR(20)	Waymo vehicle identifier
start_time	TIMESTAMP	Trip start timestamp
end_time	TIMESTAMP	Trip end timestamp

safety_interventions

Column	Type	Description
intervention_idPK	INT	Unique intervention identifier
vehicle_id	VARCHAR(20)	Vehicle reporting the intervention
trip_id	INT	Associated trip when available
occurred_at	TIMESTAMP	Intervention timestamp
intervention_type	VARCHAR(40)	Intervention classification