Rolling Average Machine Temperatures | Dataford Interview Questions

Write a SQL query to calculate the rolling average of machine temperatures over a moving seven-day window at Siemens.

Use the machines and machine_temperature_readings tables. The window includes the current reading date and the six preceding calendar days. Retain readings whose temperature is NULL, but exclude them from average calculations.

Output

One row per machine reading, with machine_id, machine_name, reading_date, temperature_c, and rolling_avg_7d.
Include only readings associated with a machine.
Order by machine_id, reading_date, and reading_id.

Schema

machines

Column	Type	Description
machine_idPK	INT	Unique machine identifier
machine_name	VARCHAR(100)	Display name of the Siemens machine

machine_temperature_readings

Column	Type	Description
reading_idPK	INT	Unique temperature reading identifier
machine_id	INT	Machine that produced the reading
reading_date	DATE	Calendar date of the reading
temperature_c	NUMERIC(5,2)	Temperature in degrees Celsius

Your question is Rolling Average Machine Temperatures. 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