Rolling Average Downtime Query | Dataford Interview Questions

Write a query using SQL window functions to calculate a rolling average of machine downtime over the last 30 days.

Use the provided machine and downtime event data. Calculate the average downtime for each machine at every recorded event, including events within the preceding 30 days and the current event. Exclude events that cannot be matched to a machine.

Output

One row per matched downtime event
Columns: machine_id, machine_name, event_id, event_timestamp, downtime_minutes, and rolling_avg_downtime
Order by machine_id, event_timestamp, and event_id

Schema

machines

Column	Type	Description
machine_idPK	INT	Unique machine identifier
machine_name	VARCHAR(100)	Machine name

downtime_events

Column	Type	Description
event_idPK	INT	Unique downtime event identifier
machine_id	INT	Machine associated with the event
event_timestamp	TIMESTAMP	Timestamp when downtime was recorded
downtime_minutes	INT	Duration of downtime in minutes

Your question is Rolling Average Downtime Query. 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