Write a SQL query to analyze store performance by joining sales, inventory, and labor tables to calculate revenue, gross margin, and labor cost by store and week at Advance Auto Parts. Include store-weeks represented in any source table, treating missing measures as zero where appropriate.
store_id, week_start, revenue, gross_margin, labor_cost, and average_inventory_units.store_id, then week_start.| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sales transaction identifier |
| store_id | INT | Advance Auto Parts store identifier |
| sale_date | DATE | Transaction date |
| units | INT | Units sold |
| unit_price | DECIMAL(10,2) | Selling price per unit |
| unit_cost | DECIMAL(10,2) | Product cost per unit |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory observation identifier |
| store_id | INT | Advance Auto Parts store identifier |
| week_start | DATE | Start date of the inventory week |
| inventory_units | INT | Units recorded in inventory |
| Column | Type | Description |
|---|---|---|
| labor_idPK | INT | Unique labor record identifier |
| store_id | INT | Advance Auto Parts store identifier |
| week_start | DATE | Start date of the labor week |
| labor_hours | DECIMAL(8,2) | Paid labor hours |
| hourly_rate | DECIMAL(8,2) | Average hourly labor rate |