Rolling Average with SQL Window Functions | Dataford Interview Questions

WSP monitors infrastructure through sensor networks used in asset and environmental monitoring. Write a PostgreSQL query that calculates a rolling 30-day average for each active sensor.

Requirements

Join sensor_readings to sensors and include only active sensors.
Return each reading with its sensor code, timestamp, and raw reading value.
Calculate the average of the current reading and all readings for the same sensor from the preceding 29 calendar days. This creates an inclusive 30-day window.
Ignore NULL reading values using PostgreSQL aggregate behavior, and order the output by sensor code and reading timestamp.

Schema

sensors

Column	Type	Description
sensor_idPK	INTEGER	Unique sensor identifier
sensor_code	VARCHAR(30)	WSP sensor reference code
location	VARCHAR(100)	Sensor installation location
is_active	BOOLEAN	Whether the sensor is currently monitored

sensor_readings

Column	Type	Description
reading_idPK	INTEGER	Unique reading identifier
sensor_id	INTEGER	Referenced sensor identifier
recorded_at	TIMESTAMP	Time when the reading was recorded
reading_value	NUMERIC(10,2)	Measured sensor value

Your question is Rolling Average with SQL Window Functions. 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