Write a SQL query to compare store-level sales, labor cost, and food waste by week and identify the stores with the largest variance from target.
Assume a store should be evaluated only when actual metrics and positive targets exist for the same week. Define overall variance as the average of the absolute percentage variances across sales, labor cost, and food waste.
week_start, store_id, store_name, the three percentage variances, overall_variance_pct, and variance_rank.week_start, then overall_variance_pct descending, then store_id.| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Unique McDonald's store identifier |
| store_name | VARCHAR(100) | Store display name |
| city | VARCHAR(80) | Store city |
| region | VARCHAR(50) | Operating region |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique weekly metrics record |
| store_id | INT | Referenced store |
| week_start | DATE | Monday starting the reporting week |
| sales | DECIMAL(12,2) | Weekly sales amount |
| labor_cost | DECIMAL(12,2) | Weekly labor cost |
| food_waste | DECIMAL(12,2) | Weekly food waste amount |
| Column | Type | Description |
|---|---|---|
| target_idPK | INT | Unique weekly target record |
| store_id | INT | Referenced store |
| week_start | DATE | Monday starting the target week |
| sales_target | DECIMAL(12,2) | Target weekly sales |
| labor_cost_target | DECIMAL(12,2) | Target weekly labor cost |
| food_waste_target | DECIMAL(12,2) | Target weekly food waste |