Given a table of robot telemetry logs and a table of maintenance events, calculate the rolling 7-day average of robot uptime at Foundation Robotics Labs.
Use logged uptime hours and subtract maintenance downtime for the same robot and calendar date. Treat dates without telemetry as zero uptime within each robot's observed date range, and treat NULL downtime as zero.
robot_id, log_date, adjusted_uptime_hours, and rolling_7_day_avg_uptime.robot_id, then log_date.| Column | Type | Description |
|---|---|---|
| telemetry_idPK | INT | Unique telemetry log identifier |
| robot_id | VARCHAR(20) | Robot identifier |
| log_date | DATE | Calendar date of the telemetry log |
| uptime_hours | NUMERIC(8,2) | Uptime recorded by the telemetry system |
| Column | Type | Description |
|---|---|---|
| maintenance_event_idPK | INT | Unique maintenance event identifier |
| robot_id | VARCHAR(20) | Robot receiving maintenance |
| maintenance_date | DATE | Calendar date of maintenance |
| downtime_hours | NUMERIC(8,2) | Hours of downtime attributed to the event |