Rolling Average Over 7 Days | Dataford Interview Questions

Your question is Rolling Average Over 7 Days. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Equinor's offshore operations teams use sensor data to monitor temperature trends in production equipment. Write a PostgreSQL query that calculates a seven-day rolling average for each sensor.

Requirements

Exclude readings whose quality_status is not VALID or whose temperature is NULL.
Aggregate valid readings to one average temperature per sensor and calendar day.
Calculate a time-based rolling average covering the current day and the preceding six calendar days, partitioned by sensor.
Return every sensor-day with a daily average and a rolling average, rounded to two decimal places.
Sort the result by sensor_id and measured_day.

Schema

sensor_readings

Column	Type	Description
reading_idPK	INTEGER	Unique sensor reading identifier
sensor_id	VARCHAR(30)	Equipment sensor identifier
measured_at	TIMESTAMPTZ	Timestamp when the reading was captured
temperature_c	NUMERIC(6,2)	Temperature measurement in degrees Celsius
quality_status	VARCHAR(20)	Quality classification for the reading

Your question is Rolling Average Over 7 Days. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Equinor's offshore operations teams use sensor data to monitor temperature trends in production equipment. Write a PostgreSQL query that calculates a seven-day rolling average for each sensor.

Requirements

Exclude readings whose quality_status is not VALID or whose temperature is NULL.
Aggregate valid readings to one average temperature per sensor and calendar day.
Calculate a time-based rolling average covering the current day and the preceding six calendar days, partitioned by sensor.
Return every sensor-day with a daily average and a rolling average, rounded to two decimal places.
Sort the result by sensor_id and measured_day.

Schema

sensor_readings

Column	Type	Description
reading_idPK	INTEGER	Unique sensor reading identifier
sensor_id	VARCHAR(30)	Equipment sensor identifier
measured_at	TIMESTAMPTZ	Timestamp when the reading was captured
temperature_c	NUMERIC(6,2)	Temperature measurement in degrees Celsius
quality_status	VARCHAR(20)	Quality classification for the reading