Rolling 30-Day Average Equipment Usage | Dataford Interview Questions

Using SQL window functions, how would you calculate a rolling average of daily equipment usage over a 30-day period?

Use the equipment_usage table. Multiple readings on the same equipment and date should be combined, and NULL usage values should not contribute to averages. Treat each 30-day period as the current date plus the preceding 29 calendar days.

Output

One row per equipment and usage date.
Columns: equipment_id, usage_date, daily_usage, and rolling_30_day_avg.
Include all dates represented in the data and order by equipment_id, then usage_date ascending.

Schema

equipment_usage

Column	Type	Description
usage_idPK	INT	Unique usage reading identifier
equipment_id	INT	Identifier of the equipment unit
usage_date	DATE	Calendar date of the usage reading
usage_hours	NUMERIC(8,2)	Equipment usage measured in hours

Your question is Rolling 30-Day Average Equipment Usage. 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

Using SQL window functions, how would you calculate a rolling average of daily equipment usage over a 30-day period?

Output

One row per equipment and usage date.
Columns: equipment_id, usage_date, daily_usage, and rolling_30_day_avg.
Include all dates represented in the data and order by equipment_id, then usage_date ascending.

Schema

equipment_usage

Column	Type	Description
usage_idPK	INT	Unique usage reading identifier
equipment_id	INT	Identifier of the equipment unit
usage_date	DATE	Calendar date of the usage reading
usage_hours	NUMERIC(8,2)	Equipment usage measured in hours

Your question is Rolling 30-Day Average Equipment Usage. 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.