Business Context
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.
Task
Write a PostgreSQL query that analyzes orders placed in January 2025 by region and order date.
Requirements
- Use the most recent inventory snapshot on or before each order date, without duplicating orders.
- Use a
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.
- Report order count, late orders, late-delivery percentage, inventory-shortage orders, orders with unavailable inventory data, and average delivery days.
- Use
LAG to show the previous available day's late-delivery percentage for the same region.
- Return one row per region and order date, ordered by region and date.
