Your question is Analyzing Operations Bottlenecks with SQL. 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.
Samsung Electronics operations leaders need a daily view of regional fulfillment performance across orders, inventory, and shipments. The analysis should distinguish delivery delays from inventory constraints and missing operational data.
Write a PostgreSQL query that analyzes orders placed in January 2025 by region and order date.
LEFT JOIN for shipments so unshipped orders remain visible, and classify orders as late when delivery exceeded the promised date or was still missing on February 1, 2025.LAG to show the previous available day's late-delivery percentage for the same region.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| region | VARCHAR(40) | Samsung sales or fulfillment region |
| product_id | VARCHAR(20) | Product identifier |
| warehouse_id | VARCHAR(20) | Fulfillment warehouse identifier |
| order_date | DATE | Date the order was placed |
| promised_date | DATE | Promised delivery date |
| quantity | INT | Units ordered |
| Column | Type | Description |
|---|---|---|
| inventory_snapshot_idPK | INT | Unique inventory snapshot identifier |
| product_id | VARCHAR(20) | Product identifier |
| warehouse_id | VARCHAR(20) | Warehouse identifier |
| snapshot_date | DATE | Date inventory was recorded |
| available_qty | INT | Units available at the snapshot |
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| order_id | INT | Related order identifier |
| shipped_date | DATE | Date shipment left the warehouse |
| delivered_date | DATE | Date shipment was delivered |
| carrier | VARCHAR(40) | Shipping carrier |