Task
You are given Hertz rental transaction data and Hertz location reference data. Write a PostgreSQL query that joins rental transactions to locations, then summarizes revenue by region. Return one row per region showing total revenue, completed rental count, and the number of transactions with missing location matches. Treat only Completed rentals as revenue-bearing, and group unmatched locations under Unknown so they still appear in the output.
Schema
rental_transactions
| column | type | description |
|---|
| transaction_id | INT | Unique rental transaction ID |
| reservation_code | VARCHAR(20) | Hertz reservation reference |
| pickup_location_id | INT | Pickup location ID from the transaction |
| rental_date | DATE | Rental start date |
| status | VARCHAR(20) | Rental status |
| revenue_amount | DECIMAL(10,2) | Revenue recorded for the transaction |
| | |
locations
| column | type | description |
|---|
| location_id | INT | Unique Hertz location ID |
| location_name | VARCHAR(100) | Hertz branch name |
| region | VARCHAR(50) | Reporting region |
| | |
Sample data
rental_transactions
| transaction_id | reservation_code | pickup_location_id | rental_date | status | revenue_amount |
|---|
| 101 | HZ1001 | 1 | 2024-01-05 | Completed | 320.00 |
| 102 | HZ1002 | 2 | 2024-01-06 | Cancelled | 0.00 |
| 103 | HZ1003 | 9 | 2024-01-07 | Completed | 450.00 |
| | | | | |
locations
| location_id | location_name | region |
|---|
| 1 | LAX Airport | West |
| 2 | SFO Downtown | West |
| 3 | JFK Airport | Northeast |
| | |
Expected output
| region | total_revenue | completed_rentals | missing_location_transactions |
|---|
| West | 860.00 | 3 | 0 |
| Unknown | 450.00 | 1 | 1 |
| Southeast | 410.00 | 2 | 0 |
| Northeast | 275.00 | 2 | 0 |
