SQL Running Totals and Averages | Dataford Interview Questions

Avathon's industrial monitoring system stores sensor readings for registered machines. Write a PostgreSQL query that summarizes readings for active machines during March 1 through March 10, 2025.

Requirements

Join the machine registry to sensor readings and include only active machines in the requested date range.
Aggregate multiple readings from the same machine and date into daily totals, counts, and averages.
Calculate a chronological running total of readings for each machine.
Calculate a trailing seven-calendar-day average, inclusive of the current date. Treat missing and NULL readings as unavailable rather than zero for the average.

Use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW for the inclusive seven-day window. Preserve dates containing only NULL readings so the running total remains unchanged and the moving average excludes those NULL values.

Schema

machines

Column	Type	Description
machine_idPK	INTEGER	Unique machine identifier
machine_code	VARCHAR(20)	Avathon machine code
asset_name	VARCHAR(100)	Human-readable asset name
status	VARCHAR(20)	Current machine status

sensor_readings

Column	Type	Description
reading_idPK	INTEGER	Unique sensor reading identifier
machine_id	INTEGER	Machine that produced the reading
reading_date	DATE	Calendar date of the reading
reading_value	NUMERIC(10,2)	Sensor measurement

Your question is SQL Running Totals and Averages. 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