Write a SQL query to analyze weekly operational performance by site, joining orders and inventory tables and calculating fulfillment rate by week for Brunswick.
Calculate weekly ordered units, fulfilled units, inventory units, and fulfillment rate for every site and week represented in orders. Treat missing fulfilled quantities as zero and avoid division by zero.
site_code, week_start, ordered_units, fulfilled_units, inventory_units, and fulfillment_rate.site_code and week_start ascending.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| site_code | VARCHAR(20) | Brunswick operational site code |
| order_date | DATE | Date the order was placed |
| quantity_ordered | INT | Units requested on the order |
| quantity_fulfilled | INT | Units fulfilled for the order |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory record identifier |
| site_code | VARCHAR(20) | Brunswick operational site code |
| inventory_date | DATE | Date of the inventory record |
| product_sku | VARCHAR(30) | Product stock keeping unit |
| available_units | INT | Units available at the site |