Your question is Optimize Slow Telemetry Queries. 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.
Explain how you would optimize a slow-running SQL query that aggregates millions of daily telemetry data points at May Mobility.
Write a PostgreSQL query that returns the requested daily depot-level telemetry summary for the specified date range. Also explain how you would investigate and improve performance at this scale.
depot_name, telemetry_date, telemetry_points, active_vehicles, total_miles, average_speed_mph, and fault_events.telemetry_date ascending, then depot_name ascending.| Column | Type | Description |
|---|---|---|
| reading_idPK | BIGINT | Unique telemetry reading identifier |
| vehicle_id | INT | Vehicle that produced the reading |
| reading_at | TIMESTAMP | Timestamp when the reading was recorded |
| miles_delta | NUMERIC(8,2) | Miles recorded since the previous reading |
| speed_mph | NUMERIC(6,2) | Vehicle speed in miles per hour |
| fault_code | VARCHAR(30) | Optional diagnostic fault code |
| Column | Type | Description |
|---|---|---|
| vehicle_idPK | INT | Unique vehicle identifier |
| depot_id | INT | Assigned service depot |
| is_active | BOOLEAN | Whether the vehicle is currently active |
| Column | Type | Description |
|---|---|---|
| depot_idPK | INT | Unique service depot identifier |
| depot_name | VARCHAR(80) | Display name of the service depot |