Task
You are given warehouse transaction data similar to what might be analyzed in Blue Yonder warehouse operations reporting. Write a PostgreSQL query to return the warehouse with the second-highest total transaction volume. Treat transaction volume as the sum of quantity for valid transactions only, and include the warehouse name and total volume in the result. If multiple warehouses tie for the second-highest total, return all of them.
Use only transactions with status = 'posted'. Warehouses with no posted transactions should not appear in the final result.
Schema
warehouses
| column | type | description |
|---|
| warehouse_id | INT | Primary key for the warehouse |
| warehouse_name | VARCHAR(100) | Warehouse name |
| region_code | VARCHAR(20) | Region identifier |
| | |
warehouse_transactions
| column | type | description |
|---|
| transaction_id | INT | Primary key for the transaction |
| warehouse_id | INT | Warehouse linked to the transaction |
| transaction_date | DATE | Date of the transaction |
| quantity | INT | Transaction quantity |
| status | VARCHAR(20) | Transaction status |
Sample data
warehouses
| warehouse_id | warehouse_name | region_code |
|---|
| 3 | Dallas Hub | SOUTH |
| 1 | Phoenix DC | WEST |
| 5 | Reno Overflow | WEST |
warehouse_transactions
| transaction_id | warehouse_id | transaction_date | quantity | status |
|---|
| 104 | 2 | 2024-02-03 | 90 | posted |
| 101 | 1 | 2024-02-01 | 120 | posted |
| 110 | 5 | 2024-02-05 | 50 | posted |
Expected output
| warehouse_name | total_transaction_volume |
|---|
| Phoenix DC | 200 |
