Rolling Average with SQL Window Functions

MediumSQL · PostgreSQL

Problem

A company 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.

Output details

Return columns in this order: sensor_code, recorded_at, reading_value, and rolling_avg_30_day.
Order by sensor_code ascending, then recorded_at ascending.
Round rolling_avg_30_day to two decimal places.

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

Tablessensorssensor_readings

A company 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.

Output details

Return columns in this order: sensor_code, recorded_at, reading_value, and rolling_avg_30_day.
Order by sensor_code ascending, then recorded_at ascending.
Round rolling_avg_30_day to two decimal places.