Your question is Rolling Failure Rate With SQL. Start with the requirements and the two 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.
Atlas Copco SMARTLINK provides operational data for monitoring industrial equipment. The service team wants to identify machines with elevated failure rates by tracking daily rates and their rolling 30-day averages.
Write a PostgreSQL query that calculates the daily failure rate and rolling average failure rate for active SMARTLINK-connected machines between 2025-01-01 and 2025-02-28.
NULL.| Column | Type | Description |
|---|---|---|
| machine_idPK | INTEGER | Unique machine identifier |
| machine_name | VARCHAR(100) | Machine name shown in SMARTLINK |
| is_active | BOOLEAN | Whether the machine is currently active |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INTEGER | Unique daily metric record |
| machine_id | INTEGER | Referenced machine identifier |
| metric_date | DATE | Date of the measurements |
| inspection_count | INTEGER | Number of inspections |
| failure_count | INTEGER | Number of failures, possibly unavailable |