Your question is Efficient Telemetry Aggregation Query. Start with the requirements and the three 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.
Caterpillar's fleet monitoring system needs a daily summary of telemetry for active machines operating at North American sites. Write a PostgreSQL query that aggregates readings while preserving active machines that have no telemetry during the reporting period.
region = 'North America' for January 1 through January 3, 2026.LEFT JOIN, and label each row as NO DATA, UNAVAILABLE, HIGH, ELEVATED, or NORMAL based on the aggregated temperature.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique Caterpillar operating or test site identifier |
| site_name | VARCHAR(120) | Name of the operating or test site |
| region | VARCHAR(40) | Geographic region used for reporting |
| Column | Type | Description |
|---|---|---|
| machine_idPK | INT | Unique machine identifier |
| model | VARCHAR(40) | Caterpillar machine model |
| site_id | INT | Operating site reference |
| status | VARCHAR(20) | Current machine lifecycle status |
| Column | Type | Description |
|---|---|---|
| reading_idPK | INT | Unique telemetry reading identifier |
| machine_id | INT | Machine that emitted the reading |
| reading_at | TIMESTAMP | Timestamp when the reading was captured |
| engine_temp_c | DECIMAL(6,2) | Engine temperature in degrees Celsius |
| fuel_rate_lph | DECIMAL(8,2) | Fuel consumption rate in liters per hour |