Your question is Rolling 30-Day Average Equipment Usage. 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.
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.
equipment_id, usage_date, daily_usage, and rolling_30_day_avg.equipment_id, then usage_date ascending.| 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 |