Write a SQL query to analyze operational performance by location, including joins across orders, inventory, and staffing tables, and summarize key business metrics by week.
Use the supplied tables and calculate weekly order volume, ordered units, completed orders, fulfillment rate, inventory units, reserved units, staffing hours, and week-over-week order change.
location, week_start, total_orders, ordered_units, completed_orders, fulfillment_rate, inventory_units, reserved_units, staffing_hours, prior_week_orders, week_over_week_order_change.location, then week_start ascending.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| location | VARCHAR(50) | Operational location |
| order_date | DATE | Date the order was placed |
| quantity | INT | Units included in the order |
| status | VARCHAR(20) | Order fulfillment status |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory snapshot identifier |
| location | VARCHAR(50) | Inventory location |
| sku_code | VARCHAR(30) | Stock keeping unit code |
| snapshot_date | DATE | Date of the inventory snapshot |
| units_on_hand | INT | Available physical units |
| units_reserved | INT | Units reserved for orders |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INT | Unique staffing record identifier |
| location | VARCHAR(50) | Staffing location |
| employee_id | INT | Employee assigned to the shift |
| shift_date | DATE | Date of the shift |
| hours_worked | DECIMAL(6,2) | Hours worked during the shift |