Write a SQL query to analyze shipment performance by trade lane at Phoenix International Freight Services by joining shipments, customers, and carrier tables to calculate on-time delivery rate, average transit time, and monthly volume.
Use delivered 2024 shipments with complete shipment dates, and exclude canceled or unmatched records.
trade_lane, carrier_name, shipment_month, on_time_delivery_rate_pct, average_transit_days, monthly_volume, and previous_month_volume.| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| customer_id | INT | Customer associated with the shipment |
| carrier_id | INT | Carrier assigned to the shipment |
| trade_lane | VARCHAR(100) | Origin and destination trade lane |
| ship_date | DATE | Date the shipment departed |
| promised_delivery_date | DATE | Committed delivery date |
| actual_delivery_date | DATE | Actual delivery date |
| status | VARCHAR(30) | Shipment lifecycle status |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(120) | Customer legal or trading name |
| customer_segment | VARCHAR(40) | Customer market segment |
| Column | Type | Description |
|---|---|---|
| carrier_idPK | INT | Unique carrier identifier |
| carrier_name | VARCHAR(120) | Carrier name |
| mode | VARCHAR(30) | Primary transportation mode |