Your question is SQL for Shipment and Delivery Reporting. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query to report daily shipment volume, on-time delivery rate, and average order cycle time by facility at McKesson.
Use the provided facility, order, and shipment data. Treat on-time rate as the percentage of delivered shipments delivered on or before the promised date. Calculate cycle time only for delivered shipments.
facility_code, shipped_date, shipment_volume, on_time_delivery_rate_pct, and avg_order_cycle_time_days.facility_code, then shipped_date ascending.| Column | Type | Description |
|---|---|---|
| facility_idPK | INT | Unique facility identifier |
| facility_code | VARCHAR(20) | Operational facility code |
| facility_name | VARCHAR(100) | Facility name |
| region | VARCHAR(50) | Geographic operating region |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique customer order identifier |
| facility_id | INT | Facility fulfilling the order |
| order_date | DATE | Date the order was placed |
| priority | VARCHAR(20) | Order service priority |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| order_id | INT | Order associated with the shipment |
| shipped_date | DATE | Date the shipment left the facility |
| promised_delivery_date | DATE | Committed delivery date |
| delivered_date | DATE | Actual delivery date |