Active Hours Between Maintenance Events SQL | Dataford Interview Questions

Write a SQL query to join a table of robot maintenance logs with a table of daily operational hours, calculating the total active hours between maintenance events for each robot at Amazon Robotics.

Treat each interval as the dates strictly after one maintenance event and strictly before the next event for the same robot. Exclude a maintenance event that has no later event.

Output

One row per maintenance interval with robot_id, maintenance_event_id, maintenance_date, next_maintenance_date, and total_active_hours.
Include intervals with no operational-hours matches as zero.
Order by robot_id, then maintenance_date.

Schema

robot_maintenance_logs

Column	Type	Description
maintenance_event_idPK	INT	Unique maintenance event identifier
robot_id	INT	Amazon Robotics robot identifier
maintenance_date	DATE	Date on which maintenance occurred

daily_operational_hours

Column	Type	Description
operational_hours_idPK	INT	Unique daily operational-hours record identifier
robot_id	INT	Amazon Robotics robot identifier
operational_date	DATE	Date represented by the operational-hours record
active_hours	NUMERIC(6,2)	Hours the robot was actively operating that day

Your question is Active Hours Between Maintenance Events SQL. Start with the requirements and the two tables 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