You are given Lyft trip records and city metadata. Write a PostgreSQL query that cleans inconsistent trip status values, joins each trip to its city, and summarizes operational performance by city for January 2024. Use the cleaned status logic to count completed trips and calculate completed-trip revenue, while excluding canceled and unknown statuses from the completed metrics.
| Column | Type | Description |
|---|---|---|
| trip_idPK | INT | Primary key for each trip |
| city_id | INT | City identifier for the trip |
| trip_date | DATE | Date the trip occurred |
| status | VARCHAR(20) | Raw trip status, may contain inconsistent casing or whitespace |
| fare_amount | DECIMAL(10,2) | Fare charged for the trip; can be NULL |
| Column | Type | Description |
|---|---|---|
| city_idPK | INT | Primary key for city metadata |
| city_name | VARCHAR(100) | City name |
| region | VARCHAR(50) | Region for reporting |