Your question is 7-Day Rolling Average with Window Functions. Start with the requirements and the four tables 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.
Siemens Healthineers operations analysts need to monitor downtime for the SOMATOM CT product line. Write a PostgreSQL query that calculates daily unplanned operational downtime and its seven-day rolling average.
SOMATOM CT product line.2025-01-01 through 2025-01-10, including dates with no downtime.| Column | Type | Description |
|---|---|---|
| product_line_idPK | INT | Product line identifier |
| product_line_name | VARCHAR(100) | Product line name |
| Column | Type | Description |
|---|---|---|
| equipment_idPK | INT | Equipment identifier |
| product_line_id | INT | Assigned product line |
| equipment_code | VARCHAR(30) | Equipment code |
| status | VARCHAR(20) | Equipment lifecycle status |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Downtime event identifier |
| equipment_id | INT | Equipment affected by the event |
| downtime_date | DATE | Date of downtime |
| downtime_minutes | NUMERIC(10,2) | Duration of downtime in minutes |
| downtime_type | VARCHAR(20) | Planned or unplanned downtime |
| Column | Type | Description |
|---|---|---|
| calendar_datePK | DATE | Date in the reporting calendar |