Write a SQL query to analyze operational performance by city, joining trip data, maintenance records, and city-level targets to identify underperforming markets for Spin.
Use the target month in city_targets as the reporting period. Include cities with no trips or maintenance records, and treat only completed trips as operational trips.
city_name, target_month, completed_trips, total_revenue, maintenance_events, maintenance_rate_pct, target_completed_trips, target_revenue, max_maintenance_rate_pct, previous_completed_trips, completed_trip_change, and failure_countfailure_count descending, then city_name ascending| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Unique trip identifier |
| scooter_id | INT | Scooter used for the trip |
| city_id | INT | Spin city identifier |
| trip_date | DATE | Date the trip occurred |
| status | VARCHAR(20) | Trip status |
| duration_minutes | INT | Trip duration in minutes |
| revenue | NUMERIC(10,2) | Revenue generated by the trip |
| Column | Type | Description |
|---|---|---|
| maintenance_idPK | INT | Unique maintenance record identifier |
| scooter_id | INT | Scooter receiving maintenance |
| city_id | INT | City where maintenance occurred |
| maintenance_date | DATE | Date maintenance was recorded |
| issue_type | VARCHAR(30) | Maintenance issue category |
| downtime_hours | NUMERIC(6,2) | Estimated scooter downtime |
| resolved_date | DATE | Date the issue was resolved |
| Column | Type | Description |
|---|---|---|
| target_idPK | INT | Unique target record identifier |
| city_id | INT | Spin city identifier |
| city_name | VARCHAR(50) | Spin city name |
| target_month | DATE | First day of the target month |
| target_completed_trips | INT | Expected completed trips |
| target_revenue | NUMERIC(10,2) | Expected trip revenue |
| max_maintenance_rate_pct | NUMERIC(6,2) | Maximum acceptable maintenance events as a percentage of completed trips |