Window Functions for Rolling Metrics | Dataford Interview Questions

Explain window functions in SQL and provide a practical use case for calculating rolling averages over time-series data.

Use the table below as the input source. Write a SQL query that returns a rolling 3-day average for each device's metric value, ordered by device and timestamp.

Output

One row per device reading
Columns: device_id, reading_ts, metric_value, rolling_3_day_avg
Include all rows from the input table
Sort by device_id, then reading_ts

Schema

device_readings

Column	Type	Description
reading_idPK	INT	Unique reading identifier
device_id	VARCHAR(50)	Device identifier
reading_ts	TIMESTAMP	Timestamp when the reading was recorded
metric_value	NUMERIC(10,2)	Measured value for the reading

Your question is Window Functions for Rolling Metrics. 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