Rolling Average SQL for Test Results | Dataford Interview Questions

TÜV SÜD quality teams monitor inspection measurements across testing facilities. Write a PostgreSQL query that calculates a rolling average of each test result over the current date and the previous 29 calendar days.

Requirements

Join test results to the facility and test catalog tables.
Partition the rolling average by facility and test type.
Use a time-based window, not a row-count window, so gaps between test dates are handled correctly.
Return the result value and rolling average for every matched test result, ordered by facility, test type, and test timestamp. Ignore NULL measurements in the average.

Schema

facilities

Column	Type	Description
facility_idPK	INTEGER	Unique testing facility identifier
facility_name	VARCHAR(100)	TÜV SÜD testing facility name

test_catalog

Column	Type	Description
test_type_idPK	INTEGER	Unique test type identifier
test_type_name	VARCHAR(100)	Inspection test name

test_results

Column	Type	Description
result_idPK	INTEGER	Unique test result identifier
facility_id	INTEGER	Facility reference
test_type_id	INTEGER	Test catalog reference
tested_at	TIMESTAMP	Test completion timestamp
result_value	NUMERIC(10,2)	Measured test result

Your question is Rolling Average SQL for Test Results. Start with the requirements and the three 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