Write a SQL query to identify late shipments by customer, joining shipment, order, and customer tables and calculating delay days. Treat a shipment as late when its delivered date is after its expected delivery date. Return only shipments with complete dates and valid customer and order matches.
customer_name, order_id, shipment_id, expected_delivery_date, delivered_date, and delay_days.customer_name, delay_days descending, then shipment_id.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(150) | Customer account name |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | Customer that placed the order |
| order_date | DATE | Date the order was placed |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| order_id | INT | Order fulfilled by the shipment |
| expected_delivery_date | DATE | Promised delivery date |
| delivered_date | DATE | Actual delivery date |