Handling Missing Time-Series Values | Dataford Interview Questions

How do you handle missing values in a time-series dataset?

Using sensor_readings, write a query that returns every calendar date between each sensor's first and last reading. Preserve non-null observations, linearly interpolate dates bounded by non-null observations, and leave values unresolved when interpolation is not possible.

Output

One row per sensor and calendar date, with sensor_id, reading_date, filled_value, and value_status.
Include observed, interpolated, or unresolved status, ordered by sensor_id and reading_date.

Schema

sensor_readings

Column	Type	Description
sensor_idPK	VARCHAR(20)	Identifier of the sensor
reading_datePK	DATE	Calendar date of the reading
measurement	NUMERIC(10,2)	Sensor measurement, which may be NULL

Your question is Handling Missing Time-Series Values. Start with the requirements and the one table 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

How do you handle missing values in a time-series dataset?

Output

One row per sensor and calendar date, with sensor_id, reading_date, filled_value, and value_status.
Include observed, interpolated, or unresolved status, ordered by sensor_id and reading_date.

Schema

sensor_readings

Column	Type	Description
sensor_idPK	VARCHAR(20)	Identifier of the sensor
reading_datePK	DATE	Calendar date of the reading
measurement	NUMERIC(10,2)	Sensor measurement, which may be NULL

Your question is Handling Missing Time-Series Values. Start with the requirements and the one table 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.