Your question is Rolling Battery Temperature by Machine. 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.
You are given Tesla battery manufacturing telemetry and asked to write a PostgreSQL query that returns the rolling average of cell_temperature_c over the prior 5 minutes for each machine_id. The rolling window should be time-based, not row-count-based, and must be partitioned by machine so readings from different machines never mix. Return one row per reading with the machine, timestamp, raw temperature, and rolling average, ordered by machine_id and reading_time.
| Column | Type | Description |
|---|---|---|
| reading_idPK | INT | Unique identifier for each telemetry reading |
| machine_id | VARCHAR(20) | Battery production machine identifier |
| reading_time | TIMESTAMP | Timestamp of the temperature reading |
| cell_temperature_c | NUMERIC(5,2) | Measured battery cell temperature in Celsius |
| sensor_status | VARCHAR(20) | Sensor health state such as OK, CALIBRATING, or OFFLINE |