Window Functions for Rolling Metrics | Dataford Interview Questions

Write a complex SQL query using window functions to compute a rolling average over a dynamic time window.

The window length is defined per reading by window_days; a NULL value uses a seven-day default. Include readings from the same account whose timestamps fall within the inclusive window ending at the current reading. NULL metric values must not affect the average.

Output

One row per reading, including readings with no matching prior data
Columns: account_name, reading_id, recorded_at, metric_value, window_days, rolling_average
Order by account_name, recorded_at, and reading_id ascending

Schema

metric_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for the reading
account_name	VARCHAR(100)	Account associated with the reading
recorded_at	TIMESTAMP	Timestamp when the metric was recorded
metric_value	NUMERIC(10,2)	Measured value used in the rolling average
window_days	INT	Inclusive lookback length in days; NULL defaults to seven

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

Write a complex SQL query using window functions to compute a rolling average over a dynamic time window.

Output

One row per reading, including readings with no matching prior data
Columns: account_name, reading_id, recorded_at, metric_value, window_days, rolling_average
Order by account_name, recorded_at, and reading_id ascending

Schema

metric_readings

Column	Type	Description
reading_idPK	INT	Unique identifier for the reading
account_name	VARCHAR(100)	Account associated with the reading
recorded_at	TIMESTAMP	Timestamp when the metric was recorded
metric_value	NUMERIC(10,2)	Measured value used in the rolling average
window_days	INT	Inclusive lookback length in days; NULL defaults to seven

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.