Find Continuous Unbooked Periods | Dataford Interview Questions

Given a table of vehicle availability intervals, write a query to find the continuous periods when a vehicle was unbooked.

Each row contains a vehicle, start and end timestamps, and a status. Treat overlapping or touching unbooked intervals as one continuous period. Ignore incomplete intervals and rows whose status is not unbooked.

Output

One row per vehicle and continuous unbooked period.
Return vehicle_id, unbooked_start, unbooked_end, and duration_hours.
Sort by vehicle_id, then unbooked_start.

Schema

vehicle_availability_intervals

Column	Type	Description
interval_idPK	INT	Unique interval identifier
vehicle_id	VARCHAR(20)	Vehicle identifier
interval_start	TIMESTAMP	Start timestamp of the availability interval
interval_end	TIMESTAMP	End timestamp of the availability interval
status	VARCHAR(20)	Interval status, such as unbooked or booked

Your question is Find Continuous Unbooked Periods. Start with the requirements and the one table in the Question tab.

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

Problem

Given a table of vehicle availability intervals, write a query to find the continuous periods when a vehicle was unbooked.

Output

One row per vehicle and continuous unbooked period.
Return vehicle_id, unbooked_start, unbooked_end, and duration_hours.
Sort by vehicle_id, then unbooked_start.

Schema

vehicle_availability_intervals

Column	Type	Description
interval_idPK	INT	Unique interval identifier
vehicle_id	VARCHAR(20)	Vehicle identifier
interval_start	TIMESTAMP	Start timestamp of the availability interval
interval_end	TIMESTAMP	End timestamp of the availability interval
status	VARCHAR(20)	Interval status, such as unbooked or booked

Your question is Find Continuous Unbooked Periods. Start with the requirements and the one table in the Question tab.

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