Write a SQL query to analyze warehouse performance by day, including total orders, on-time shipments, and late shipments by site.
Use the sites, orders, and shipments tables. Include orders without a completed shipment, and exclude shipments that do not match an order.
site_id, site_name, order_date, total_orders, on_time_shipments, and late_shipments.site_id, then order_date ascending.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique warehouse site identifier |
| site_name | VARCHAR(100) | Warehouse site name |
| region | VARCHAR(50) | Operational region for the site |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique customer order identifier |
| site_id | INT | Site that processed the order |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(30) | Current order status |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| order_id | INT | Order associated with the shipment |
| promised_delivery_date | DATE | Promised delivery date |
| delivered_date | DATE | Actual delivery date |
| carrier | VARCHAR(50) | Shipping carrier |